SCRIPT by PROCEDURE

---------------------------PRODUCE SCRIPT through PROCEDURE------------------------

alter PROC usp_get_inserts(@table_name varchar(50)) as
BEGIN
--usp_get_inserts '[TABLE NAME]'
declare @cols varchar(1000) =''
declare @ins varchar(1000)=''
declare @cols_type varchar(max)=''
select @cols=@cols+','+c.name ,@cols_type= @cols_type
+ '+'',''+' +case when t.name in('int','money') then 'cast('+ c.name + ' as varchar(10))' else 'char(39) +'+c.name + '+ CHAR(39)' end
from sys.columns c
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
where object_id=object_id(@table_name)
set @cols = right(@cols,len(@cols)-1)
set @cols_type = right(@cols_type,len(@cols_type)-5)
set @ins= 'INSERT INTO '+@table_name+'(' + @cols + ') VALUES('
exec('select '''+ @ins + '''+'+ @cols_type + '+'')'' from ' + @table_name)
END

SPLIT FUNCTION

----------------------SPLIT names  using STRING FUNCTIONS--------------------------


/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 08/06/2012 16:16:27 ******/

SET

ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER ON

GO

-- select distinct mls_mail_to from gpc_mails where MLS_MESSAGE_FROM='NC' and mls_mail_status is null

create function [dbo].[Split] (@String varchar(8000), @Delimiter char(1))

Returns

@Results Table (Items varchar(8000))

As

Begin

Declare @Index int

Declare @Slice varchar(8000)

Select @Index = 1

If @String Is NULL Return

While @Index != 0

Begin

Select @Index = CharIndex(@Delimiter, @String)

If (@Index != 0)

Select @Slice = left(@String, @Index - 1)

else

Select @Slice = @String

Insert into @Results(Items) Values (@Slice)

Select @String = right(@String, Len(@String) - @Index)

If Len(@String) = 0 break

End

Return

End

 --select * from dbo.Split('1,vagalla,SURESH,reddy',',,')

Difference between TWO dates

---------------------------------Difference between TWO dates--------------------

DECLARE @date dateTIME, @tmpdate datetime, @years int, @months int, @days int
SET @date = '06-20-1989'

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years, @months, @days

ANS: 24 1 27

SQL to XML & XML to SQL

--------convert sql to xml------------------

SET @XML=(SELECT * FROM t1 FOR XML AUTO,ELEMENTS,ROOT('MAIN'))

SELECT @XML

------------------CONVERT XML to SQL TABLE---------------

ALTER PROC usp_xml(@str XML)
AS
BEGIN
SELECT X.Y.value('id[1]','int')AS ID,X.Y.value('name[1]','varchar(50)')AS NAME FROM @str.nodes('/MAIN/t1') X(Y)
END
DECLARE @EXML XML='<MAIN>
  <t1>
    <id>1</id>
    <name>suresh</name>
  </t1>
  <t1>
    <id>2</id>
    <name>naresh</name>
  </t1>
  <t1>
    <id>4</id>
    <name>naresh</name>
  </t1>
 
</MAIN>'
EXEC usp_xml @EXML

**Note: t1-table name
             id-column id
            name-column name

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