Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Unexpected truncation in FETCH

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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...