PL/SQL Tips for Sql Server

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

2009-04-25T02:42:00.000-07:00

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

2009-04-23T00:13:00.000-07:00

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

2008-08-22T03:26:00.000-07:00

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

2008-08-05T01:18:00.000-07:00

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

2 B B

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

)

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

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

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

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

2008-07-24T23:01:00.000-07:00

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

CREATE FUNCTION [dbo].[SplitString]

(

@str varchar(100),

@delchar varchar(1)

)

RETURNS @mytbl table(val varchar(100))

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

2008-07-12T01:33:00.000-07:00

First create one table for employee..

USE [test]

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[employee](

[empid] [int] NOT NULL,

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

[parentid] [int] NOT NULL

) ON [PRIMARY]

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)

(

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)

(

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

2008-04-30T05:36:00.000-07:00

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

2008-04-14T03:37:00.000-07:00

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

2008-04-10T05:49:00.000-07:00

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

2008-04-10T05:37:00.000-07:00

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

2008-04-10T01:09:00.000-07:00

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

2008-04-07T03:47:00.000-07:00

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

2008-04-07T03:25:00.000-07:00

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

2008-04-07T02:14:00.000-07:00

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

2008-04-07T00:29:00.000-07:00

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

2008-04-07T00:26:00.000-07:00

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

2008-04-07T00:16:00.001-07:00

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

2008-03-07T05:43:00.000-08:00

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

2008-03-06T02:57:00.000-08:00

--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

2008-03-06T02:08:00.000-08:00

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

Fibonacci Series Script in T-SQL

2008-03-06T01:58:00.000-08:00

Here this script is displying Fibonacci Series from 1 to 1000.

declare @a int
declare @b int
declare @c int
declare @res varchar(200)
set @a = 0
set @b = 1
set @c = 0
set @res = ' '

while(@c<=1000)
begin
set @a = @b
Set @b = @c
set @c = @a + @b
set @res = @res + cast(@c as varchar) + ','
end

print reverse(substring(reverse(@res),2,len(@res)))

Output will be...
1,1,2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597

Full Text Search

2008-02-15T00:25:00.005-08:00

Here first of all assign Full text search on ur database by select database than select property by right click than select File options exist in left side and than select check box for Use full text indexing.

Than u have to add that tables for which u want to give Full text search by this select table than right click than select Full Text Index than select Define Full Text Index and complite the wizard. Here in this wizard u have to add those field which u want to search by Full text search.

After completion to assign all table in full text catelog u have to use four function which are used in Full text search....

contains()
freetext()
containstable()
freetexttable()

this four function give proper result by Full text search...

Replication

2008-02-15T00:25:00.003-08:00

Database replication is the creation and maintenance of multiple copies of the same database. In most implementations of database replication, one database server maintains the master copy of the database and additional database servers maintain slave copies of the database. Database writes are sent to the master database server and are then replicated by the slave database servers. Database reads are divided among all of the database servers, which results in a large performance advantage due to load sharing. In addition, database replication can also improve availability because the slave database servers can be configured to take over the master role if the master database server becomes unavailable.

Trigger

2008-02-15T00:25:00.001-08:00

A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.

There are two classes of triggers, they are either "row triggers" or "statement triggers". With row triggers you can define an action for every row of a table, while statement triggers occur only once per INSERT, UPDATE, or DELETE statement. Triggers cannot be used to audit data retrieval via SELECT statements.

Each class can be of several types. There are "BEFORE triggers" and "AFTER triggers" which identifies the time of execution of the trigger. There is also an "INSTEAD OF trigger" which is a trigger that will execute instead of the triggering statement.

There are typically three triggering events that cause triggers to 'fire':

INSERT event (as a new record is being inserted into the database).
UPDATE event (as a record is being changed).
DELETE event (as a record is being deleted).
The trigger is used to automate DML condition process.

The major features and effects of database triggers are that they:

do not accept parameters or arguments (but may store affected-data in temporary tables)
cannot perform commit or rollback operations because they are part of the triggering SQL statement (only through autonomous transactions)
can cause mutating table errors, if they are poorly written.

Indexing

2008-02-15T00:24:00.003-08:00

Clustered Indexes
A printed phone directory is a great example of a clustered index. Each entry in the directory represents one row of the table. A table can have only one clustered index. That is because a clustered index is the actual table sorted in order of the cluster key. At first glance, you might think that inserting a new row into the table will require all the rows after the inserted row to be moved on the disk. Luckily, this is not the case. The row will have to be inserted into the correct data page, and this might require a page split if there is not enough room on the page for the new row. A list of pointers maintains the order between the pages, so the rows in other pages will not have to actually move.

The primary key of the phone directory is the phone number. Usually the primary key is used as the clustering key as well, but this is not the case in our example. The cluster key in the phone directory is a combination of last name and first name. How would you find a friend's phone number if you knew the last and first name? Easy, open the book approximately to the section of the book that contains the entry. If your friend's last name starts with an "F", you will search near the beginning of the book, if an "S", you will search towards the back. You can use the names printed at the top of the page to quickly locate the page with the listing. You then drill down to the section of the correct page till you find the last name you are looking for. Now you can use the first name to choose the correct listing. The phone number is right there next to the name. It probably takes more time describe the process than to actually do it. Using the last name plus first name to find the number is called a clustered index seek.

Lets say you wanted to find all the people who have the last name of "Smith" for a family reunion. How quickly could you locate all the names? Of course, it would take a matter of seconds to find all of them grouped together, possibly over a few pages. What if you wanted to locate everyone with the first name of "Jeff" in the book? Could it be done? Of course it could, but you would have to look at every entry in the entire book because the first name is the second column in our cluster key. This is called a clustered index scan, a very expensive operation on a big table.

Here is an example using one of the tables in AdventureWorks. The Sales.SalesOrderDetail table has a clustered index on SalesOrderID plus SalesOrderDetailID. Take a look at the graphical estimated execution plan (Figure 2) of the batch paying particular attention to the Query cost when either the first or second column in the cluster key is used.

Prime No Script in T-SQL

2008-02-15T00:23:00.001-08:00

Here this script is check weather no from 1 to 100 is Prime or not if no is Prime than print it.

SET NOCOUNT on
go
declare @val bigint
declare @tval bigint
declare @oval bigint
declare @rem bigint
declare @res bigint
declare @divby int
declare @fg bit

set @res = 0
set @val = 7
set @tval = @val
set @divby = 2
set @fg = 'true'
--

declare @inputTable table(_val int)
declare @i int
set @i = 1

while(@i<=100) begin insert into @inputTable values(@i) set @i = @i + 1 end declare ibuffer cursor fast_forward for select _val from @inputTable open ibuffer fetch next from ibuffer into @val while (@@fetch_status != -1) BEGIN set @divby = 2 set @fg = 'true' set @tval = @val set @oval = @val set @res = 0 while(@divby > 1 and @divby <= @val -1) begin set @rem = @tval % @divby if(@rem = 0) begin set @fg = 'false' break end set @divby = @divby + 1 end if(@fg = 'true') begin print 'Given no is prime --- > ' + cast(@oval as varchar)
end

fetch next from ibuffer into @val
End
deallocate ibuffer

Output will be...
Given no is prime --- > 1
Given no is prime --- > 2
Given no is prime --- > 3
Given no is prime --- > 5
Given no is prime --- > 7
Given no is prime --- > 11
Given no is prime --- > 13
Given no is prime --- > 17
Given no is prime --- > 19
Given no is prime --- > 23
Given no is prime --- > 29
Given no is prime --- > 31
Given no is prime --- > 37
Given no is prime --- > 41
Given no is prime --- > 43
Given no is prime --- > 47
Given no is prime --- > 53
Given no is prime --- > 59
Given no is prime --- > 61
Given no is prime --- > 67
Given no is prime --- > 71
Given no is prime --- > 73
Given no is prime --- > 79
Given no is prime --- > 83
Given no is prime --- > 89
Given no is prime --- > 97

Digit Sum Script in T-SQL

2008-02-15T00:22:00.001-08:00

Here this script it used for check which no from 1 to 100 have sum of digit

--Here this script it used for check which no from 1 to 100 have sum of digit
--is equal to @outputTot.
--Example if @outputTot set to 5 means
-- first 5 = digit sum is 5.
-- than 14 = 1 + 4 = digit sum is 5.
-- than 23 = 2 + 3 = digit sum is 5.
-- than 32,41,50
-- than 59 = 5 + 9 = 14 than 1 + 4 = digit sum is 5.
-- above example is calculate digit sum untill sum is between 1 to 9.

SET NOCOUNT on
go
declare @val bigint
declare @tval bigint
declare @oval bigint
declare @rem bigint
declare @res bigint
declare @outputTot bigint

set @res = 0
set @val = 43
set @tval = @val
set @outputTot = 3

declare @inputTable table(_val int)
declare @i int
set @i = 1

while(@i<=100) begin insert into @inputTable values(@i) set @i = @i + 1 end declare ibuffer cursor fast_forward for select _val from @inputTable open ibuffer fetch next from ibuffer into @val while (@@fetch_status != -1) BEGIN set @tval = @val set @oval = @val set @res = 0 while(@val > 9)
begin
set @res = 0
while(@tval > 0)
begin
set @rem = @tval % 10
set @res = @res + @rem
set @tval = @tval / 10
end

set @val = @res
set @tval = @val

end
if(@res = @outputTot)
begin
print 'Total digit sum is '+ cast(@outputTot as varchar) +' of ' + cast(@oval as varchar)
end

fetch next from ibuffer into @val
End
deallocate ibuffer

Output will be...
Total digit sum is 3 of 12
Total digit sum is 3 of 21
Total digit sum is 3 of 30
Total digit sum is 3 of 39
Total digit sum is 3 of 48
Total digit sum is 3 of 57
Total digit sum is 3 of 66
Total digit sum is 3 of 75
Total digit sum is 3 of 84
Total digit sum is 3 of 93

Sorting Script in T-SQL

2008-02-15T00:21:00.000-08:00

Here i create temporary table for sorting columns.

SET NOCOUNT on
go
declare @tempTbl table(i int,strName varchar(20))

insert into @tempTbl values(1,'Manish')
insert into @tempTbl values(2,'Supratim')
insert into @tempTbl values(3,'Anil')
insert into @tempTbl values(4,'Pranav')
insert into @tempTbl values(5,'Chirag')
insert into @tempTbl values(6,'Paresh')
insert into @tempTbl values(7,'Nilesh')
insert into @tempTbl values(8,'Bhavesh')
insert into @tempTbl values(9,'Hitesh')
insert into @tempTbl values(10,'Subhash')
insert into @tempTbl values(11,'Anish')
insert into @tempTbl values(12,'Ania')

declare @i int
declare @j int
declare @pos int
declare @strName varchar(20)
set @i = 0
set @j = 1

declare ibuffer cursor fast_forward for

select i,strName from @tempTbl

open ibuffer
fetch next from ibuffer into @pos,@strName
while (@@fetch_status != -1)
begin
declare @ipos int
declare @istrName varchar(20)
declare iibuffer cursor fast_forward for

select i,strName from @tempTbl
where i > @pos
open iibuffer
fetch next from iibuffer into @ipos,@istrName
while (@@fetch_status != -1)
begin
if(@strName > @istrName)
begin
update @tempTbl
set strName = @strName
where i = @ipos

update @tempTbl
set strName = @istrName
where i = @pos

set @strName = @istrName
print '------Updated------'
end

fetch next from iibuffer into @ipos,@istrName
end
deallocate iibuffer
fetch next from ibuffer into @pos,@strName
end
deallocate ibuffer

select strName from @tempTbl

Output will be...
Ania
Anil
Anish
Bhavesh
Chirag
Hitesh
Manish
Nilesh
Paresh
Pranav
Subhash
Supratim

T-SQL Scripts for Table

2008-02-15T00:20:00.000-08:00

----------------------------------------------------------------------------------------------
Here in many cases we have to first find table contain record or not.
If table contains record than update that row is going on. If table not contain record than insert new row in table.
So in this case we first find that table contains records or not and than we decide to take which operation have to perform on that table.
Like…….
DECLARE @COLUMNID INT
DECLARE @NEWVALUE
DECLARE @cnt INT
SELECT @cnt = COUNT(COLUMNNAME) FROM TABLENAME WHERE COLUMNID = @COLUMNID

IF(@cnt > 0)
BEGIN
UPDATE TABLENAME
SET COLUMNNAME = @NEWVALUE
WHERE COLUMNID = @COLUMNID
END
ELSE
BEGIN
INSERT INTO TABLENAME
(COMUMNNAME)
VALUES(@NEWVALUE)
END

This will done shortly with the use of exists() function that return ‘true’ if select query return records
Otherwise return ‘false’
Like……..

IF EXISTS(SELECT COLUMNNAME FROM TABLENAME WHERE COLUMNID = @COLUMNID)
BEGIN
UPDATE TABLENAME
SET COLUMNNAME = @NEWVALUE
WHERE COLUMNID = @COLUMNID
END
ELSE
BEGIN
INSERT INTO TABLENAME
(COMUMNNAME)
VALUES(@NEWVALUE)
END
----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------
Here sql statements for
1.creating table
2.inserting record
3.updating record
4.deleting record
5.fetching records
6.droping table

For example i take Student as a entity and perform above statements
for it.

1.Creating table

CREATE TABLE [dbo].[Student](
[studentID] [bigint] IDENTITY(1,1) NOT NULL,
[studentName] [varchar](50) NOT NULL,
[studentAge] [int] NOT NULL,
[studentSex] [bit] NOT NULL,
[studentBirthDate] [datetime] NOT NULL,
[studentAddress] [varchar](200) NULL,
CONSTRAINT [PK_StudentMaster] PRIMARY KEY CLUSTERED
(
[studentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

2.Inserting record

INSERT INTO [dbo].[Student]
([studentName]
,[studentAge]
,[studentSex]
,[studentBirthDate]
,[studentAddress])
VALUES
('Paresh Patel'
,25
,'true'
,'1982-06-23 00:00:00.000'
,'Ahmedabad')

3.Updating record

UPDATE [dbo].[Student]
SET [studentName] = 'Nirmit Oza'
,[studentAge] = 25
,[studentSex] = 'false'
,[studentBirthDate] = '1982-06-23 00:00:00.000'
,[studentAddress] = 'Mehsana'
WHERE [studentID] = 1

4.Deleting record

DELETE FROM [dbo].[Student]
WHERE [studentID] = 2

5.Fetching records

SELECT [studentID]
,[studentName]
,[studentAge]
,[studentSex]
,[studentBirthDate]
,[studentAddress]
FROM [dbo].[Student]

6.Droping table

DROP TABLE [dbo].[Student]
----------------------------------------------------------------------------------------------