using cursors and displaying row numbers

Question

Hi

I have declared the following cursor and used a local variable @RowNo to print the number of each each row.

  declare TheCursor cursor 
  for select productName from products
  declare @RowNo int 
  declare @productName nvarchar(50)
  set @RowNo = 1
  open TheCursor
  fetch next from TheCursor into @productName
  print @RowNo
  print @productName
  set @RowNo = @RowNo+1
  set @productName=''

 while @@FETCH_STATUS=0
    begin
     fetch next from TheCursor into @productName
     print @RowNo
     print @productName
     set @RowNo = @RowNo+1
     set @productName=''
    end
 close TheCursor
 deallocate TheCursor

I am trying to find another ways to specify a number to each row and display it in the consul. I find the function Row_number() and used it like select ROW_NUMBER() over (order by (select 0)) As Rownumber to do it. And now I want to know is it imposible that I use the KEYSET in my cursor to do it? How can I do it with KEYSET?

Answer 1

I don't completely understand what you're trying to achieve - but you could wrap your select statement including the ROW_NUMBER() function into a CTE (common table expression) and then go from there - no messy cursor needed, that really doesn't scale well:

WITH YourSelection AS
(
   SELECT    
      ProductName,
      ROW_NUMBER() OVER(ORDER BY ProductName) AS 'RowNum'
   FROM dbo.Products
)
SELECT ProductName, RowNum
FROM YourSelection

That should give you the product names, sorted by ProductName, and corresponding row numbers, too.