October 23, 2013 by Kenneth Fisher
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.