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

Amount Into Word Script in T-SQL

Here this script display amount in word of @acceptedAmount = 111111111.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CalRupees]
(
@rupee int
)
RETURNS varchar(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @result varchar(100)
-- Add the T-SQL statements to compute the return value here
--SET @result = 'Anil'
if(@rupee >= 1 AND @rupee <= 10)
begin
if((@rupee % 10) = 1) set @result = 'One'
if((@rupee % 10) = 2) set @result = 'Two'
if((@rupee % 10) = 3) set @result = 'Three'
if((@rupee % 10) = 4) set @result = 'Four'
if((@rupee % 10) = 5) set @result = 'Five'
if((@rupee % 10) = 6) set @result = 'Six'
if((@rupee % 10) = 7) set @result = 'Seven'
if((@rupee % 10) = 8) set @result = 'Eight'
if((@rupee % 10) = 9) set @result = 'Nine'
if((@rupee % 10) = 0) set @result = 'Ten'
--set @result = cast(@rupee as varchar)
END
if(@rupee > 9 AND @rupee < 20)
begin
if(@rupee = 11) set @result = 'Eleven'
if(@rupee = 12) set @result = 'Twelve'
if(@rupee = 13) set @result = 'Thirteen'
if(@rupee = 14) set @result = 'Forteen'
if(@rupee = 15) set @result = 'Fifteen'
if(@rupee = 16) set @result = 'Sixteen'
if(@rupee = 17) set @result = 'Seventeen'
if(@rupee = 18) set @result = 'Eighteen'
if(@rupee = 19) set @result = 'Nineteen'
end
if(@rupee > 20 AND (@rupee / 10) = 2 AND (@rupee % 10) = 0) set @result = 'Twenty'
if(@rupee > 20 AND (@rupee / 10) = 3 AND (@rupee % 10) = 0) set @result = 'Thirty'
if(@rupee > 20 AND (@rupee / 10) = 4 AND (@rupee % 10) = 0) set @result = 'Forty'
if(@rupee > 20 AND (@rupee / 10) = 5 AND (@rupee % 10) = 0) set @result = 'Fifty'
if(@rupee > 20 AND (@rupee / 10) = 6 AND (@rupee % 10) = 0) set @result = 'Sixty'
if(@rupee > 20 AND (@rupee / 10) = 7 AND (@rupee % 10) = 0) set @result = 'Seventy'
if(@rupee > 20 AND (@rupee / 10) = 8 AND (@rupee % 10) = 0) set @result = 'Eighty'
if(@rupee > 20 AND (@rupee / 10) = 9 AND (@rupee % 10) = 0) set @result = 'Ninty'

if(@rupee > 20 AND (@rupee / 10) = 2 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Twenty One'
if((@rupee % 10) = 2) set @result = 'Twenty Two'
if((@rupee % 10) = 3) set @result = 'Twenty Three'
if((@rupee % 10) = 4) set @result = 'Twenty Four'
if((@rupee % 10) = 5) set @result = 'Twenty Five'
if((@rupee % 10) = 6) set @result = 'Twenty Six'
if((@rupee % 10) = 7) set @result = 'Twenty Seven'
if((@rupee % 10) = 8) set @result = 'Twenty Eight'
if((@rupee % 10) = 9) set @result = 'Twenty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 3 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Thirty One'
if((@rupee % 10) = 2) set @result = 'Thirty Two'
if((@rupee % 10) = 3) set @result = 'Thirty Three'
if((@rupee % 10) = 4) set @result = 'Thirty Four'
if((@rupee % 10) = 5) set @result = 'Thirty Five'
if((@rupee % 10) = 6) set @result = 'Thirty Six'
if((@rupee % 10) = 7) set @result = 'Thirty Seven'
if((@rupee % 10) = 8) set @result = 'Thirty Eight'
if((@rupee % 10) = 9) set @result = 'Thirty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 4 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Forty One'
if((@rupee % 10) = 2) set @result = 'Forty Two'
if((@rupee % 10) = 3) set @result = 'Forty Three'
if((@rupee % 10) = 4) set @result = 'Forty Four'
if((@rupee % 10) = 5) set @result = 'Forty Five'
if((@rupee % 10) = 6) set @result = 'Forty Six'
if((@rupee % 10) = 7) set @result = 'Forty Seven'
if((@rupee % 10) = 8) set @result = 'Forty Eight'
if((@rupee % 10) = 9) set @result = 'Forty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 5 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Fifty One'
if((@rupee % 10) = 2) set @result = 'Fifty Two'
if((@rupee % 10) = 3) set @result = 'Fifty Three'
if((@rupee % 10) = 4) set @result = 'Fifty Four'
if((@rupee % 10) = 5) set @result = 'Fifty Five'
if((@rupee % 10) = 6) set @result = 'Fifty Six'
if((@rupee % 10) = 7) set @result = 'Fifty Seven'
if((@rupee % 10) = 8) set @result = 'Fifty Eight'
if((@rupee % 10) = 9) set @result = 'Fifty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 6 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Sixty One'
if((@rupee % 10) = 2) set @result = 'Sixty Two'
if((@rupee % 10) = 3) set @result = 'Sixty Three'
if((@rupee % 10) = 4) set @result = 'Sixty Four'
if((@rupee % 10) = 5) set @result = 'Sixty Five'
if((@rupee % 10) = 6) set @result = 'Sixty Six'
if((@rupee % 10) = 7) set @result = 'Sixty Seven'
if((@rupee % 10) = 8) set @result = 'Sixty Eight'
if((@rupee % 10) = 9) set @result = 'Sixty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 7 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Seventy One'
if((@rupee % 10) = 2) set @result = 'Seventy Two'
if((@rupee % 10) = 3) set @result = 'Seventy Three'
if((@rupee % 10) = 4) set @result = 'Seventy Four'
if((@rupee % 10) = 5) set @result = 'Seventy Five'
if((@rupee % 10) = 6) set @result = 'Seventy Six'
if((@rupee % 10) = 7) set @result = 'Seventy Seven'
if((@rupee % 10) = 8) set @result = 'Seventy Eight'
if((@rupee % 10) = 9) set @result = 'Seventy Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 8 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Eighty One'
if((@rupee % 10) = 2) set @result = 'Eighty Two'
if((@rupee % 10) = 3) set @result = 'Eighty Three'
if((@rupee % 10) = 4) set @result = 'Eighty Four'
if((@rupee % 10) = 5) set @result = 'Eighty Five'
if((@rupee % 10) = 6) set @result = 'Eighty Six'
if((@rupee % 10) = 7) set @result = 'Eighty Seven'
if((@rupee % 10) = 8) set @result = 'Eighty Eight'
if((@rupee % 10) = 9) set @result = 'Eighty Nine'
end
if(@rupee > 20 AND (@rupee / 10) = 9 AND (@rupee % 10) != 0)
begin
if((@rupee % 10) = 1) set @result = 'Ninty One'
if((@rupee % 10) = 2) set @result = 'Ninty Two'
if((@rupee % 10) = 3) set @result = 'Ninty Three'
if((@rupee % 10) = 4) set @result = 'Ninty Four'
if((@rupee % 10) = 5) set @result = 'Ninty Five'
if((@rupee % 10) = 6) set @result = 'Ninty Six'
if((@rupee % 10) = 7) set @result = 'Ninty Seven'
if((@rupee % 10) = 8) set @result = 'Ninty Eight'
if((@rupee % 10) = 9) set @result = 'Ninty Nine'
end
RETURN @result
END
GO


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


declare @acceptedAmount bigint
declare @rem bigint
declare @result varchar(500)
set @result =''
set @acceptedAmount = 111111111
print ' Amount : '+ cast(@acceptedAmount as varchar)
if((@acceptedAmount / 10000000) > 0) --print 'Caror'
begin
set @rem = @acceptedAmount / 10000000
set @acceptedAmount = @acceptedAmount % 10000000
set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Caror'
end
if((@acceptedAmount / 100000) > 0) --print 'Lack'
begin
set @rem = @acceptedAmount / 100000
set @acceptedAmount = @acceptedAmount % 100000
set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Lack'
end
if((@acceptedAmount / 1000) > 0) --print 'Thousand'
begin
set @rem = @acceptedAmount / 1000
set @acceptedAmount = @acceptedAmount % 1000
set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Thousand'
end
if((@acceptedAmount / 100) > 0) --print 'Hundred'
begin
set @rem = @acceptedAmount / 100
set @acceptedAmount = @acceptedAmount % 100
set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Hundred'
end
if((@acceptedAmount % 10) > 0) --print 'Ten'
begin
set @rem = @acceptedAmount % 100
--print @amount
set @result = @result + ' ' + [dbo].[CalRupees](@rem)
end
set @result = @result + ' ' + ' Rs.'
print ' Amount in word :'
print @result

-------------------------------------------------------------------------------------------------
drop function [dbo].[CalRupees]
-------------------------------------------------------------------------------------------------

Output will be...

Amount : 111111111
Amount in word :
Eleven Caror Eleven Lack Eleven Thousand One Hundred Eleven Rs.