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

Try.....Catch Script in T-SQL

Here this script show how to use Try.....Catch Block in T-SQL.


begin try
print 1/0
print 'Sucessfully executed try block.'
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)
end catch

Output will be...


Error No : 8134
Error Severity : 16
Error State : 1
Error Line : 2
Error Message : Divide by zero error encounter

Swapping Numeric Value Script in T-SQL

Here this script swapping the value of @a and @b variable.

declare @a int
declare @b int
declare @c int
set @a = 123
set @b = 543
print 'Before swapping a : ' + cast(@a as varchar)
print 'Before swapping b : ' + cast(@b as varchar)
set @a = @a + @b
set @b = @a - @b
set @a = @a - @b
print 'After swapping a : ' + cast(@a as varchar)
print 'After swapping b : ' + cast(@b as varchar)

Output will be...

Before swapping a : 123
Before swapping b : 543
After swapping a : 543
After swapping b : 123