As a developer we all know that using cursors in sql server is always an expensive deal. So here I am explaining how can we replace a cursor using while loop in our sql code to do a iteration on given dataset(table).
Here is the example..
I have employee table and I want to loop over employee table. my table structure is given below.
create table employee(Eid int, Ename varchar(100),Dept varchar(50))
insert into employee values(1001,’Neeraj’,’IT’)
insert into employee values(1002,’Raj’,’Finance’)
insert into employee values(1003,’Shyam’,’HR’)
insert into employee values(1004,’Vikas’,’Payroll’)
insert into employee values(1005,’Rachit’,’Legal’)
Now we have 5 records in employee table and i want to loop over employee table.
it can be done in two ways..first way is Cursor that is quite expensive thing in terms of performance.
so what i would do to replace cursor.
I will create a table variable with list of columns that i need during loop and i will create a identity column as well for identifying each row uniquely.
create @temp table (rowid int identity,name varchar(100))
then i will insert all the data on which i want to do loop in the table variable.
insert into @temp(name) select ename from employee
then i will declare two variables @mincounter and @maxcounter to contain minimum and maximum rowid value from table variable.
and below is the code to loop over the dataset
declare@maxcounter as int
delcare @mincounter as int
declare @ename as varchar(100)
select@mincounter=min(rowid),@maxcounter=max(rowid) from @temp
select @ename=name from @temp where rowid=@mincounter
line of code which you want to process based onemployee name
I hope this will help you to replace cursors in your TSQL code…have a great day..
680 total views, 2 views today