Search code examples
sql-serversql-server-2008cursor

Understanding SQL Cusros syntax

I am looking at an example for CURSOR on the following link:

http://blog.sqlauthority.com/2008/03/05/sql-server-simple-example-of-cursor-sample-cursor-part-2/

USE AdventureWorks
GO

DECLARE @ProductID INT
DECLARE @getProductID CURSOR

SET @getProductID = CURSOR FOR
    SELECT ProductID
    FROM Production.Product

OPEN @getProductID
FETCH NEXT FROM @getProductID INTO @ProductID

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @ProductID
    FETCH NEXT FROM @getProductID INTO @ProductID
END

CLOSE @getProductID
DEALLOCATE @getProductID

GO

It is a very simple example however I fail to understand following things:

  • Why is there a FETCH twice in the definition?
  • We see it happening when it opens the cursor and then inside the while loop there is same exact FETCH happening.
  • Is there anything we can do to make the code little bit more cleaner and less redundant?
Solution

  • The first fetch is to populate your variables before you start your loop.

    The second fetch is to populate those variables for the next iteration of the loop. The @@FETCH_STATUS = 0 part of the loop will drop you out if you have run out of things for your variables.

    Unfortunately that is as clear as I think you can make it for cursors.