SQL Server Table Variables To Eliminate Cursors

Here's a quick tip that allows you to duplicate the functionality of a cursor without using one in SQL Server 2000.

declare @RowCnt int
declare @MaxRows int
declare @Email nvarchar(255)
declare @rc int

select @RowCnt = 1

declare @Import table
(
rownum int IDENTITY (1, 1) Primary key NOT NULL ,
Email nvarchar(255)
)

insert into @Import (Email) values ('blah@blah.com')
insert into @Import (Email) values ('blahblah@blah.com')

select @MaxRows=count(*) from @Import

while @RowCnt <= @MaxRows
begin

select @rc=0

select @Email = Email
from @Import
where rownum = @RowCnt

print @Email

Select @RowCnt = @RowCnt + 1

end

 

By Robbe Morris   Popularity  (2485 Views)
Picture
Biography - Robbe Morris
Robbe has been a Microsoft MVP in C# since 2004. He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.  Robbe also loves to scuba dive and go deep sea fishing in the Florida Keys or off the coast of Daytona Beach. Microsoft MVP