Tuesday, 19 December 2006

Cursor functionality via table variable and loop

-- cursor functionality WITHOUT using a cursor

declare @CurrentRow int
declare @TotalRows int
declare @FirstName nvarchar(255)

select @CurrentRow = 1

declare @TableVariable table
(
UniqueRowID int IDENTITY (1, 1) Primary key NOT NULL ,
FirstName nvarchar(255)
)

insert into @TableVariable (FirstName) values ('Adam')
insert into @TableVariable (FirstName) values ('Bill')
insert into @TableVariable (FirstName) values ('Charlie')
insert into @TableVariable (FirstName) values ('Dennis')

select @TotalRows=count(*) from @TableVariable

while @CurrentRow <= @TotalRows  
begin   
select @FirstName = FirstName   
from @TableVariable   
where UniqueRowID = @CurrentRow    
print @FirstName    
select @CurrentRow = @CurrentRow + 1  
end 

No comments: