How to replace cursor with while loop in sql

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

while @mincounter<=@maxcounter

begin

select @ename=name from @temp where rowid=@mincounter

line of code which you want to process based on employee name

set @mincounter=@mincounter+1

end

I hope this will help you to replace cursors in your TSQL code...have a great day..

declare @maxcounter as int

delcare @mincounter as int

declare @ename as varchar(100)

select @mincounter=min(rowid),@maxcounter=max(rowid) from @temp

while @mincounter<=@maxcounter

begin

      select @ename=name from @temp where rowid=@mincounter

      line of code which you want to process based on employee name

      set @mincounter=@mincounter+1

end