Yes I’m talking about FETCH as in a cursor, yes everyone hates cursors. But you know cursors are like anything else. It’s good to know everything you can and they do have their place. In fact there are two very specific places that I see cursors.

First: You need to do a loop.

Someone just thought “Loops are bad. Batch processing is always faster!”, however to the best of my knowledge there is no way to batch process stored procedure executions. If you need to loop through a list and run a stored procedure with each iteration then there is no way to batch this. In this particular case I’m not sure any speed issues between a cursor and a while loop have any real significance. If your SP takes 1 minute for each iteration then does the fraction of a second difference between cursor and any other loop make any real difference?

Second: You are working with legacy code.

It would be nice if we could re-write all legacy code but it isn’t going to happen. So guess what? You are going to see cursors.

This last is what brings me to my story. I recently had the task of automating a piece of code. And I was even given the leeway to do some re-writing while I was at it. One particular stored procedure I saw created a cursor and then did an insert or update on each iteration of the cursor loop. I of course changed this into a INSERT INTO SELECT FROM where appropriate and a MERGE where appropriate. So imagine my surprise when the new code failed. Not because I had made a mistake but because of some behavior of the FETCH command that I wasn’t expecting. It turns out that when data is loaded into variables using a FETCH command it will truncate them to fit the variable length, and here is the kicker, without giving an error!

-- Setup example code
CREATE TABLE Cursor_Test (Long_String varchar(20))
GO
INSERT INTO Cursor_Test VALUES ('A Long Test String.')
GO

-- Cursor example
DECLARE Cursor_Test_Cursor CURSOR
READ_ONLY
FOR SELECT Long_String FROM Cursor_Test
DECLARE @Short_String varchar(10)
OPEN Cursor_Test_Cursor
FETCH NEXT FROM Cursor_Test_Cursor INTO @Short_String
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT @Short_String
END
FETCH NEXT FROM Cursor_Test_Cursor INTO @Short_String
END
CLOSE Cursor_Test_Cursor
DEALLOCATE Cursor_Test_Cursor
GO

Once I realized what was going on I realized I probably shouldn’t have been all that surprised as this is the same behavior as loading a variable using a SELECT statement.

-- Load data via SELECT
DECLARE @Short_String varchar(10)
SELECT @Short_String = Long_String FROM Cursor_Test
PRINT @Short_String
GO

-- Clean up code
DROP TABLE Cursor_Test

Please everyone keep your fingers crossed for me that this doesn’t become a huge issue where we have to go back and not only fix all of the legacy code but re-run all of the data loads for the last year or so. Oh, and be careful when using cursors that your variable lengths are correct.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, FETCH, language sql, microsoft sql server, sql statements, T-SQL