Split Function Script in T-SQL

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE FUNCTION [dbo].[SplitString]

(

@str varchar(100),

@delchar varchar(1)

)

RETURNS @mytbl table(val varchar(100)) 

AS

begin

declare @tstr varchar(100)

declare @i int

declare @j int

declare @strlen int

declare @charat varchar(1)

set @strlen = len(@str)

set @i = 1

set @tstr = ''

while(@i <= @strlen)

begin

set @charat = substring(@str,@i,1)

if(@charat <> @delchar)

set @tstr = @tstr + @charat

else

begin

insert into @mytbl values(@tstr)

set @tstr = ''

end

set @i = @i + 1

end

insert into @mytbl values(@tstr)

return

end

----------------------------------------------------------------------------------------------

To use above function write:

select * from SplitString('anil,tejas,nilesh,paresh,yashesh,manish',',')

Output will be...

anil

tejas

nilesh

paresh

yashesh

manish

Use of With...As clause Script in T-SQL

First create one table for employee..

USE [test]

GO

/****** Object:  Table [dbo].[employee]    Script Date: 07/12/2008 14:03:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[employee](

[empid] [int] NOT NULL,

[empname] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL,

[parentid] [int] NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

then enter some records in above table...

1 Tejas 0

2 Anil  3

3 Paresh 5

4 Manish 1

5 Lafdo 4

6 Yashesh 2

-----------------------------------------------------------------------------------------------------------------

then execute below query to find upper-level parents and low-level parents...

select * from dbo.employee;

with RecursionCTE (empid,empname,parentid,upperlevel)

 as

 (

   select empid,empname,parentid,

          convert(varchar(100),'') upperlevel

      from dbo.employee

      where parentid = 0

   union all

   select R1.empid,R1.empname,

          R1.parentid,

          case when DataLength(R2.upperlevel) > 0

                    then convert(varchar(100),(select empname from dbo.employee where empid = R1.empid)

       + ' -> '+ R2.upperlevel )

                    else (select empname from dbo.employee where empid = R1.empid)

                    end as upperlevel

      from dbo.employee as R1

     join RecursionCTE as R2 on R1.parentid = R2.empid

  )

select empid,empname,parentid,upperlevel from RecursionCTE order by empid asc ;

with RecursionCTE (empid,empname,parentid,lowerlevel)

 as

 (

   select parentid,empname,empid,

          convert(varchar(100),'') lowerlevel

      from dbo.employee

   union all

   select R1.empid,R1.empname,

          R1.parentid,

          case when DataLength(R2.lowerlevel) > 0

                    then convert(varchar(100),(select empname from dbo.employee where empid = R1.empid)

       + ' -> '+ R2.lowerlevel )

                    else (select empname from dbo.employee where empid = R1.empid)

                    end as lowerlevel

      from dbo.employee as R1

     join RecursionCTE as R2 on R1.empid = R2.parentid

  )

select empid,empname,parentid,max(lowerlevel) as lowerlevel

from RecursionCTE 

where datalength(lowerlevel) > 0

group by empid,empname,parentid order by empid asc 

-----------------------------------------------------------------------------------------------------------------

Output will be...

All records of above table..

1 Tejas 0

2 Anil 3

3 Paresh 5

4 Manish 1

5 Lafdo 4

6 Yashesh 2

All records with upper-level parents..

1 Tejas 0

2 Anil 3 Anil -> Paresh -> Lafdo -> Manish

3 Paresh 5 Paresh -> Lafdo -> Manish

4 Manish 1 Manish

5 Lafdo 4 Lafdo -> Manish

6 Yashesh 2 Yashesh -> Anil -> Paresh -> Lafdo -> Manish

All records with low-level parents..

1 Tejas 0 Tejas -> Manish -> Lafdo -> Paresh -> Anil -> Yashesh

2 Anil 3 Anil -> Yashesh

3 Paresh 5 Paresh -> Anil -> Yashesh

4 Manish 1 Manish -> Lafdo -> Paresh -> Anil -> Yashesh

5 Lafdo 4 Lafdo -> Paresh -> Anil -> Yashesh

6 Yashesh 2 Yashesh

How Many Sunday In Give Month & Year Script in T-SQL

Here this script calculate total Sunday in give month and give year @month = 6 and @year = 2008.


declare @year int
declare @month int
declare @totaldays int
declare @isLeapYear bit
declare @firstDayofmonth varchar(20)
declare @date varchar(20)
set @isLeapYear = 'false'
--------------------------------------------------
--- Here change year and month for diff result
--------------------------------------------------
set @year = 2008
set @month = 6
--------------------------------------------------
set @totaldays = 31
set @date = cast(@year as varchar) + '-' + cast(@month as varchar) + '-01'
if(@year % 4 = 0)
set @isLeapYear = 'true'
if(@year % 100 = 0)
if(@year % 400 = 0)
set @isLeapYear = 'false'
if(@month = 1) set @totaldays = 31
if(@month = 2 )
begin
if(@isLeapYear = 'true') set @totaldays = 29
else set @totaldays = 28
end
if(@month = 3) set @totaldays = 31
if(@month = 4) set @totaldays = 30
if(@month = 5) set @totaldays = 31
if(@month = 6) set @totaldays = 30
if(@month = 7) set @totaldays = 31
if(@month = 8) set @totaldays = 31
if(@month = 9) set @totaldays = 30
if(@month = 10) set @totaldays = 31
if(@month = 11) set @totaldays = 30
if(@month = 12) set @totaldays = 31
set @firstDayofmonth = DATENAME(dw,@date)
print 'Given Year : ' + cast(@year as varchar)
print 'Given Month : ' + cast(@month as varchar)
print 'Total days : ' + cast(@totaldays as varchar)
print 'First day is : ' + @firstDayofmonth

declare @i int
declare @cnt int
declare @currday varchar(20)
set @i = 1
set @cnt = 0
set @currday = DATENAME(dw,@date)
while(@i <= @totaldays)
begin
if(@currday = 'Sunday') set @cnt = @cnt + 1
set @currday = DATENAME(dw,dateadd(dd,1,@date))
set @date = dateadd(dd,1,@date)
set @i = @i + 1
end
print 'Total Sunday in give month are : ' + cast(@cnt as varchar)

Output will be...


Given Year : 2008
Given Month : 6
Total days : 30
First day is : Sunday
Total Sunday in give month are : 5

Draw Ractangle Script in T-SQL

Here this script display ractangle in output window @size = 17.

declare @i int

declare @j int

declare @k int

declare @totLine int

declare @res varchar(100)

declare @showingCharacter varchar(1)

set @showingCharacter = '#'

set @totLine = 20

set @i = 1

set @j = 1

set @k = 1

set @res = ''

while(@k <= (@totLine))

begin

set @res = @res + '# '

set @k = @k + 1

end

print @res

set @res = ''

while(@i <= (@totLine - 2))

begin

set @res =  '# ' + REPLICATE('  ',@totLine - 2) + '# '

set @i = @i + 1

print @res

end

print @res

set @res = ''

set @k = 1

while(@k <= (@totLine))

begin

set @res = @res + '# '

set @k = @k + 1

end

print @res

set @res = ''

Output will be...

# # # # # # # # # # # # # # # # # # # # 

#                                                                 # 

#                                                                 # 

#                                                                 # 

#                                                                 # 

#                                                                 # 

#                                                                 # 

#                                                                 # 

#                                                                 # 

#                                                                 # 

#                                                                 # 

#                                                                 # 

#                                                                 # 

#                                                                 # 

#                                                                 #  

#                                                                 # 

#                                                                 # 

#                                                                 # 

#                                                                 # 

#                                                                 # 

# # # # # # # # # # # # # # # # # # # # 

Table Copy Script in T-SQL

Script for copy records with table structure into new table from other existing table without creating new table

SELECT a.*
INTO dbo.newTable
FROM dbo.oldTable a


Script for copy only records into new table from other existing table.Here new table must be create before executing this script.

INSERT INTO dbo.newTable
SELECT Name,Address,Phone,Email_address
FROM dbo.oldTable

Cursor Script in T-SQL

Here this script show how to use Cursor...


declare @studentId int
declare @studentName varchar(50)
declare ibuffer cursor fast_forward for
select studentId,studentName
from Student
open ibuffer
fetch next from ibuffer into @studentId,@studentName
while(@@fetch_status!= -1)
begin
print 'Student Id : ' + cast(@studentId as varchar)
print 'Stuednt Name : ' + @studentName
fetch next from ibuffer into @studentId,@studentName
end
deallocate ibuffer

Output will be...


Student Id : 1
Stuednt Name : Anil Desai
Student Id : 2
Stuednt Name : Paresh Patel
Student Id : 3
Stuednt Name : Mitesh Modi
Student Id : 4
Stuednt Name : Nirmit Modi
Student Id : 5
Stuednt Name : Bharat Modi

Begin Transaction Script in T-SQL

Here this script show how to use Begin Transaction...


begin transaction
begin try
--...
--...
--Sql Statements
--...
--...
-- Here Successfully complite all above statements.
commit transaction
end try
begin catch
print 'Error No : ' + cast(ERROR_NUMBER() as varchar)
print 'Error Severity : ' + cast(ERROR_SEVERITY() as varchar)
print 'Error State : ' + cast(ERROR_STATE() as varchar)
print 'Error Line : ' + cast(ERROR_LINE() as varchar)
print 'Error Message : ' + cast(ERROR_MESSAGE() as varchar)
--Here if any statements create problem in executing than rollback
--all statement that executed before it.
rollback transaction
end catch