CREATE PROCEDURE IMPLICITLY

USE [database_name]
GO
SELECT * FROM sys.tables
--------------------------------------------------------------------------
USE master
GO
SELECT * FROM sys.tables
SELECT * FROM sys.types
SELECT * FROM sys.columns

------------------INNER JOIN on  system_type_id-----------------------------------
SELECT * FROM SYS.COLUMNS sc INNER JOIN sys.types st ON          sc.system_type_id=st.system_type_id where object_id=object_id('emp')

--concatenation column_name,data_type,size

declare @concatname varchar(1000)=''
select @concatname=@concatname+',@'+SC.name+' '+ST.name+'('+cast(st.max_length as varchar(50))+')' from sys.columns SC INNER JOIN sys.types ST ON SC.system_type_id=ST.system_type_id WHERE OBJECT_ID=OBJECT_ID('EMP')
print RIGHT(@concatname,LEN(@concatname)-1)
----------------------CREATE PROCEDURE IMPLICITILY-------------------------------------------
CREATE PROC val_insert(@tblname VARCHAR(100),@tbl_insert VARCHAR(100))
AS
BEGIN
DECLARE @parameter NVARCHAR(1000)=''
SELECT @parameter=@parameter+','+s.name+''
FROM sys.columns s inner join sys.types t on s.system_type_id=t.system_type_id WHERE OBJECT_ID=OBJECT_ID('EMP')
--print RIGHT(@parameter,LEN(@parameter))

DECLARE @concat NVARCHAR(1000)=''
SELECT @concat=@concat+'@'+s.name+' '+t.name+''+'('+cast(t.max_length AS VARCHAR(10))+')'+','
FROM sys.columns s inner join sys.types t on s.system_type_id=t.system_type_id WHERE OBJECT_ID=OBJECT_ID('EMP')
PRINT 'CREATE PROC '+@tblname+'('print LEFT(RIGHT(@concat,LEN(@concat)),LEN(@concat)-1)+')'
PRINT 'AS'
PRINT 'BEGIN'
PRINT +' '+'insert into'+' '+@tbl_insert+' '+'values('print RIGHT(@parameter,LEN(@parameter)-1) +')'
PRINT 'END'
PRINT 'EXEC'+' '+@tblname
END
EXEC val_insert 'EMP','TBL'

PRINT TOTAL SALARY using CURSOR

--PRINT TOTAL SALARY using CURSOR 

CREATE TABLE Employee

(

 EmpID int PRIMARY KEY,

 EmpName varchar (50) NOT NULL,

 Salary int NOT NULL,

 Address varchar (200) NOT NULL,

)

GO

INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida')

INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi')

INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun')

INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida')

INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon')

GO

SELECT * FROM Employee 

------------------CURSOR-------------------

alter proc usp_emp_totalsal as

begin

set nocount on

declare @id int 

declare @name varchar(50)

declare @Each_row_sal int=0

declare cursortable cursor

for select empID,empname,salary from Employee

open cursortable

fetch next from cursortable into @id,@name,@Each_row_sal

declare @totalsal int=0

while(@@FETCH_STATUS=0)

begin

print @totalsal

set @totalsal=@totalsal+@Each_row_sal

fetch next from cursortable into @id,@name,@Each_row_sal

end

close cursortable

deallocate cursortable

print @totalsal

set nocount off

end

exec usp_emp_totalsal

Polyndrom in sql

PRINTING STRING IN REVERSE without using STRING FUNCTION SUBSTRING()

DECLARE @str VARCHAR(20)='vagalla suresh reddy',@oldstr VARCHAR(20),@newstr VARCHAR(20)='',@I INT=1,@C INT=0
SELECT @OLDSTR=@STR,@C=LEN(@STR)
WHILE(@I<=@C)
         BEGIN
                SELECT @newstr=@newstr+SUBSTRING(@str,@C,1)
         SET @C=@C-1
END
PRINT @newstr

ANS:ydder hserus allagav