Table Variables As An Alternative to Cursors

,

For performance concerns or when the need arises to do some data manipulation/updates/inserts  or any other code that requires a loop, use a table variable instead of the ever expensive cursor.

declare @somevar varchar(30) --optional filter paramter 
declare @maxrow int, @ii int,@param1 int ,@param2 varchar(50)
Declare @tablevar table (TID int IDENTITY, Param1 int,Param2  varchar(50))

insert @tablevar
select ColA,ColB from sometable where whatever = @somevar
select @maxrow=@@rowcount,@ii =1

While @ii<= @maxrow
begin
    ---insert your desired code here or for example
   select @param1 = Param1,@param2=Param2 from @tablevar   where tid=@ii
   update someothertable
   set somefield = @param2
   where anotherfield = @param1
   set @ii = @ii +1   
end

/*
Please note that for very large result sets it is better to create a temp table than to use table variables.
I sincerely hope that this is of some use to someone as it has helped me tremendously
*/

Rate

Share

Share

Rate