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