About Me

My photo
જય વાળીનાથ
T-SQL is a basic of structure query language. So I always want to learn T-SQL in different way to get best performance in query. And its my passion.

Friday, February 15, 2008

Sorting Script in T-SQL

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

No comments: