Thursday, September 23, 2010

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!

5 comments:

  1. It's very ugly code. '1=1' means that loop never ends, but then suddenly it breaks. That's confusing

    ReplyDelete
  2. Visions of FORTRAN

    ReplyDelete
  3. It is not ugly, it is elegant and brilliant.

    ReplyDelete
  4. There is a way to avoid using 1=1. I recommend avoiding using 1=1 because it makes the code less readable.

    A better way to make such a loop is something like this:

    DECLARE @LoopBreak INT
    SET @LoopBreak = 0
    DECLARE Employee_Cursor CURSOR FOR
    SELECT BusinessEntityID, JobTitle
    FROM AdventureWorks2008R2.HumanResources.Employee;
    OPEN Employee_Cursor;
    WHILE LoopBreak = 0
    BEGIN
    FETCH NEXT FROM Employee_Cursor;
    IF @@FETCH_STATUS = 0
    BEGIN
    -- do something here
    END
    ELSE
    SET @LoopBreak = 1
    END
    END;
    CLOSE Employee_Cursor;
    DEALLOCATE Employee_Cursor;

    ReplyDelete
  5. The option is having an if/else inside a loop or having the first fetch before loop starts. It is not very efficient to do if/else for lots of repetitions when the loop can achieve that easily.

    ReplyDelete