case sensitive searches....Script in T-SQL

When we are using where clause for conditional search sql using case insensitive and produce result.

But some time we need to search case sensitive then i show you some example for it...

First create one table.

CREATE TABLE [dbo].[checkName](

[Id] [int] IDENTITY(1,1) PRIMARY KEY,

[Cname] [varchar](100)

and execute above script.

then insert some records in above table.

insert into checkName (Cname) values('Anil')

insert into checkName (Cname) values('aNil')

insert into checkName (Cname) values('anIl')

insert into checkName (Cname) values('aniL')

insert into checkName (Cname) values('AnIl')

execute above script.

then execute below script for finding value of 'aniL'

select *

from checkName

where BINARY_CHECKSUM(Cname) = BINARY_CHECKSUM('aniL')

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

select *

from checkName

where Cname = 'aniL' COLLATE SQL_Latin1_General_CP1_CS_AS

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

select *

from checkName

where CAST(Cname AS varbinary(100)) = CAST('aniL' AS varbinary(100))

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

Output will be....

Id Cname

4 aniL

Using ROW_NUMBER() to paginate your data ... Script in T-SQL

Here making paginating functionality by Sql Server side...

Here first of all set @totalPageSize, which set how many row should be display...

Then set @pageIndex , which set the pageindex,

declare @totalPageSize int

declare @pageIndex int

declare @startIndex int

declare @endIndex int

set @totalPageSize = 10

set @pageIndex = 1

set @startIndex = (@pageIndex * @totalPageSize) + 1

set @endIndex = @startIndex + @totalPageSize

select top (@totalPageSize) z.*

from

(

select

ROW_NUMBER() OVER(ORDER BY userId DESC) AS 'RowNumber',

userId, userFirstName, userLastName,

userAddress1, userAddress2, userPhone1,

userPhone2

from [userInfo]

) as z

where z.RowNumber between @startIndex and @endIndex

RowNumber userId userFirstName userLastName userAddress1 userAddress2 userPhone1 userPhone2

11 45 A B sdf sdf 34343

12 44 G W ds ds 1121

13 43 K T Ahd Ahd 121 12

14 42 T Y Ahd Ahd 12323 34234

17 38 ehilly Bons Ahmedabad

18 37 dhilly Bons Ahmedabad

19 36 chilly Bons Ahmedabad

20 35 bhilly Bons Ahmedabad

Finding nth highest values... Script in T-SQL

The following script will gives you the nth highest values.

It will find nth highest salary from employee table

which has empid,salary fields.

Set value of @nthhighest variable to whatever number you want

to assign. Result will be according to the specified value.

declare @nthhighest int

set @nthhighest = 5

select * from employee order by salary desc

select top 1 salary from 

(

select top (@nthhighest) salary from employee

order by salary desc

) as employee

order by salary 

SELECT max(salary) FROM employee WHERE salary NOT IN 

(

SELECT top (@nthhighest - 1)  salary FROM employee order by salary desc

)

Delete duplicates rows of table when there is no any unique column exist Script in T-SQL

Here when there are many duplicates row in table and

table do not have any unique columns

example.

suppose one table 'test' have three column.

1.tid 

2.tname 

3.tcity

and this table contain records like.

tid tname tcity

1 A G

1 A G

1 A G

1 A G

2 B B

2 B B

3 C V

3 C V

3 C V

3 C V

3 C V

Here when group by query fire like.

select tid,tname,tcity

from test

group by tid,tname,tcity

output of above query is.

tid tname tcity

1 A G

2 B B

3 C V

So here total three records exists

So when u want to delete duplicats records

from this type of table, i show i idea for it

like..

create table test

(tid int not null,

tname varchar(50) not null,

tcity varchar(50) not null

)

go

go

insert test values(1,'A','G')

insert test values(1,'A','G')

insert test values(1,'A','G')

insert test values(1,'A','G')

insert test values(2,'B','B')

insert test values(2,'B','B')

insert test values(3,'C','V')

insert test values(3,'C','V')

insert test values(3,'C','V')

insert test values(3,'C','V')

insert test values(3,'C','V')

go

Here i show u one query which delete duplicats rows

where there is not any unique key..

 

 

delete   maindata

from 

(

select ROW_NUMBER() OVER (ORDER BY tid,tname,tcity DESC) as rowid,

itt.tid,itt.tname,itt.tcity

from ttest itt

) as maindata

where maindata.rowid not in

(

select

rowid

from (

select ROW_NUMBER() OVER (ORDER BY tid,tname,tcity DESC) as rowid,

itt.tid,itt.tname,itt.tcity

from ttest itt

) as datawithRow

where rowid in 

(

select

top 1 rowid

from (

select ROW_NUMBER() OVER (ORDER BY  tid,tname,tcity DESC) as rowid,

itt.tid,itt.tname,itt.tcity

from ttest itt

) as idatawithRow

where

idatawithRow.tid = datawithRow.tid and

idatawithRow.tname = datawithRow.tname and

idatawithRow.tcity = datawithRow.tcity

) and

datawithRow.tid = maindata.tid and

datawithRow.tname = maindata.tname and

datawithRow.tcity = maindata.tcity

)

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

After executing delete query

select tid,tname,tcity

from test

group by tid,tname,tcity

Output will be...

tid tname tcity

1 A G

2 B B

3 C V

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

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.

Date Into Word Script in T-SQL

Here this script display date into word of @acceptedDate = getdate().



declare @acceptedDate datetime
declare @result varchar(30)
declare @day int
declare @month int
declare @year int
set @acceptedDate = getdate()
declare @tempDate varchar(20)
set @tempDate = convert(varchar,@acceptedDate,101)
print 'Input Date :' + cast(@tempDate as varchar)
set @month = cast(substring(@tempDate,1,2) as int)
set @day = cast(substring(@tempDate,4,2) as int)
set @year = cast(substring(@tempDate,7,4) as int)
if((@day % 10) = 1) set @result = cast(@day as varchar) + 'st'
if((@day % 10) = 2) set @result = cast(@day as varchar) + 'nd'
if((@day % 10) = 3) set @result = cast(@day as varchar) + 'rd'
if((@day % 10) > 3) set @result = cast(@day as varchar) + 'th'
if(@month = 1 ) set @result = @result + ' ' + 'Jan'
if(@month = 2 ) set @result = @result + ' ' + 'Feb'
if(@month = 3 ) set @result = @result + ' ' + 'Mar'
if(@month = 4 ) set @result = @result + ' ' + 'Apr'
if(@month = 5 ) set @result = @result + ' ' + 'May'
if(@month = 6 ) set @result = @result + ' ' + 'Jun'
if(@month = 7 ) set @result = @result + ' ' + 'Jul'
if(@month = 8) set @result = @result + ' ' + 'Aug'
if(@month = 9 ) set @result = @result + ' ' + 'Sep'
if(@month = 10 ) set @result = @result + ' ' + 'Oct'
if(@month = 11 ) set @result = @result + ' ' + 'Nov'
if(@month = 12 ) set @result = @result + ' ' + 'Dec'
set @result = @result + ' ' + cast(@year as varchar)
print 'Date into word :' + DATENAME(dw,@acceptedDate) + ',' + @result

Output will be...


Input Date :04/14/2008
Date into word :Monday,14th Apr 2008

Sum Of Digits Script in T-SQL

Here this script display sum of digits of @acceptedNo = 123.


declare @acceptedNo int
declare @result int
declare @rem int
set @acceptedNo = 123
set @result = 0
print 'Input no :' + cast(@acceptedNo as varchar)
while(@acceptedNo != 0)
begin
set @rem = @acceptedNo % 10
set @result = (@result + @rem )
set @acceptedNo = @acceptedNo / 10
end
print 'Sum of digits of Input no : ' + cast(@result as varchar)

Output will be...

Input no :123
Sum of digits of Input no : 6

Reverse No Script in T-SQL

Here this script display reverse no of @acceptedNo = 123.


declare @acceptedNo int
declare @result int
declare @rem int
set @acceptedNo = 123
set @result = 0
print 'Input no :' + cast(@acceptedNo as varchar)
while(@acceptedNo != 0)
begin
set @rem = @acceptedNo % 10
set @result = (@result * 10) + (@rem )
set @acceptedNo = @acceptedNo / 10
end
print 'Reverse no of Input no : ' + cast(@result as varchar)

Output will be...

Input no :123
Reverse no of Input no : 321

Rectangular Star Script in T-SQL

Here this script display rectangle with @showingCharacter = '*'.


declare @i int
declare @j int
declare @k int
declare @totLine int
declare @res varchar(100)
declare @showingCharacter varchar(1)
set @showingCharacter = '*'
set @totLine = 10
set @i = 1
set @j = 1
set @k = 1
set @res = ''
while(@i <= @totLine)
begin
while(@k <= (@totLine) - @i)
begin
set @res = @res + ' '
set @k = @k + 1
end
while(@j <= @i)
begin
set @res = @res + @showingCharacter + ' '
set @j = @j + 1
end
set @k = 1
while(@k <= (@totLine) - @i)
begin
set @res = @res + ' '
set @k = @k + 1
end
print @res
set @res = ''
set @j = 1
set @k = 1
set @i = @i + 1
end

Output will be...
^
^ ^
^ ^ ^
^ ^ ^ ^
^ ^ ^ ^ ^
^ ^ ^ ^ ^ ^
^ ^ ^ ^ ^ ^ ^
^ ^ ^ ^ ^ ^ ^ ^
^ ^ ^ ^ ^ ^ ^ ^ ^
^ ^ ^ ^ ^ ^ ^ ^ ^ ^

Leap Year Calculation Script in T-SQL

--Here this script is check weather give year is leap year or not.

declare @year int
declare @isLeapYear bit
set @isLeapYear = 'false'
set @year = 2004
print 'Given year is : ' + cast(@year as varchar)

if(@year % 4 = 0)
set @isLeapYear = 'true'

if(@year % 100 = 0)
if(@year % 400 = 0)
set @isLeapYear = 'false'

if(@isLeapYear = 'true')
print cast(@year as varchar) + ' is a leap year.'
else
print cast(@year as varchar) + ' is not a leap year.'

Output will be...
Given year is : 2004
2004 is a leap year.

String Concatenation Script in T-SQL

Here this script is used for concatenates one or more string in to one string.


declare @str1 varchar(10)
declare @str2 varchar(10)
declare @str3 varchar(20)
declare @i int
set @i = 1
set @str1 = 'Mitesh'
set @str2 = 'Pupple'
set @str3 = ''
print 'string first : ' + @str1
print 'string second : ' + @str2
while(@i <= len(@str1))
begin
set @str3 = @str3 + substring(@str1,@i,1)
set @i = @i + 1
end
set @i = 1
while(@i <= len(@str2))
begin
set @str3 = @str3 + substring(@str2,@i,1)
set @i = @i + 1
end
print 'concatanation string : ' +@str3

Output will be...
string first : Mitesh
string second : Pupple
concatanation string : MiteshPupple