database question

Question

Hi, I have written the bellow cursor :

declare myCursor cursor 
  for select productID, productName from products
 declare @productID int 
 declare @productName nvarchar(50)

  open myCursor
  fetch next from myCursor into @productID,@productName
 print @productID
 print @productName
 set @productID=0
  set @productName=''

  while @@FETCH_STATUS=0
  begin
    fetch next from myCursor into @productID,@productName
    print @productID
    print @productName
    set @productID=0
    set @productName=''

  end
 close myCursor
 deallocate myCursor

I want it to have another column named RowNomber which display the number of each row while executing the cursor. Should I declare another varriabl equal with 1 and plus it with 1 ( +1) in the begin-end block? Is there any beter way to do it? ( i am using sql server 2008)

Answer 1

The easiest way is probably as you suggest.

An alternative would be to add it to the select statement as follows

select ROW_NUMBER() over (order by (select 0)) As Rownumber,
    productID, productName from products

Answer 2

Incrementing a local variable would be fine.

Another option would be to use the ROW_NUMBER() ranking function in your SELECT. Something like this:

select productID, productName
     , ROW_NUMBER() OVER(ORDER BY productID) AS rownum 
  from products