Don’t Repeat the FETCH Statement with SQL Server Cursors

If you are anything like me, then you are always trying to avoid repeating the same code whenever possible. One thing that has always bugged me is how all of the T-SQL code examples for using cursors in SQL Server use the FETCH statement right after opening the cursor and then repeat the exact same FETCH statement again inside the WHILE loop itself. Using those examples, if you ever need to update the list of columns you are fetching then you have to edit the code in two places. Here I provide you with an alternative -- there is another way!

In order to do this, I take a slightly different approach. I create a WHILE loop where the test condition is always true, FETCH, and then I BREAK out of the WHILE loop if the @@FETCH_STATUS was not zero. In the code sample below, see the highlighted sections of the before and after code:

-- Two FETCH statements, straight from MSDN...
DECLARE Employee_Cursor CURSOR FOR
SELECT BusinessEntityID, JobTitle
FROM AdventureWorks2008R2.HumanResources.Employee;
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor;
WHILE @@FETCH_STATUS = 0
   BEGIN
      -- do something here
      FETCH NEXT FROM Employee_Cursor;
   END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;

-- Only one FETCH statement!
DECLARE Employee_Cursor CURSOR FOR
SELECT BusinessEntityID, JobTitle
FROM AdventureWorks2008R2.HumanResources.Employee;
OPEN Employee_Cursor;
WHILE 1=1
   BEGIN
      FETCH NEXT FROM Employee_Cursor;
      IF @@FETCH_STATUS != 0 BREAK
      -- do something here
   END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;

 

Note that the WHILE condition is “1=1”; this is simply an expression that always evaluates to true. One is always equal to one -- T-SQL does not have a “true” value like C#. You could use another expression, as long it always evaluates to true.

Just don’t forget to check the FETCH_STATUS and BREAK out of the loop! This code is more succinct than the examples that use the FETCH statement twice (especially if it is a large FETCH statement) and I use it many production systems without any issues. Now you only have to have one FETCH statement when you need to use cursors in your T-SQL code. Enjoy!