Replacing CURSORS in SQL server

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 380
the below first part of the code is displaying( printing) top 5 roleNames from a table and it is using CURSORS to looping thru each record
	declare @RoleName nchar(15)
declare @RowNum int
declare RoleList cursor for
select top 5 rolename from luRole
OPEN RoleList
FETCH NEXT FROM RoleList INTO @RoleName
set @RowNum = 0

WHILE @@FETCH_STATUS = 0
BEGIN
set @RowNum = @RowNum + 1
print cast(@RowNum as char(1)) + ' ' + @RoleName
FETCH NEXT FROM RoleList INTO @RoleName
END
CLOSE RoleList
DEALLOCATE RoleList

GO


Cursor can degrade the performnace of the query. So better to make use of the WHILE loop and the below query will results the same output as above... For simpliying the logic i used only printing top 5 records from the table.


declare @RoleName nchar(30), @RoleId int
declare @RowNum int
select top 1 @RoleName=RoleName, @RoleId = RoleID from dbo.luRole ORDER BY RoleID
set @RowNum = 0
WHILE @RowNum < 5
BEGIN
set @RowNum = @RowNum + 1
print cast(@RowNum as char(1)) + ' ' + @RoleName
select top 1 @RoleName=rolename from dbo.luRole
where roleID > @RowNum
END

Comments or Responses

Login to post response