question

Utiger avatar image
Utiger asked

FAST_FORWARD CURSOR Loop ends unexpected

I have a procedure with a FAST_FORWARD CURSOR and I don't understand the behaviour of the Loop. Can somebody explain me why this loop does end and why the result is different whether I drop and recreate the table or I delete just the data before I execute the procedure? Here is the code of my test: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Test1]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[sp_Test1] GO CREATE PROCEDURE [dbo].[sp_Test1] as DECLARE @ColA nvarchar(100), @i Int; DECLARE @MyCursor CURSOR; SET @i = 1; SET @MyCursor = CURSOR FAST_FORWARD FOR SELECT colA FROM TabA; OPEN @MyCursor; WHILE (1 = 1) BEGIN FETCH NEXT FROM @MyCursor INTO @ColA; PRINT @@FETCH_STATUS; IF @@FETCH_STATUS <> 0 BREAK; SET @i = @i + 1; PRINT '@i = ' + CAST(@i AS VARCHAR); INSERT INTO TabA values ('Test' + CAST(@i as varchar)); END CLOSE @MyCursor DEALLOCATE @MyCursor GO DROP TABLE TabA; CREATE TABLE TabA (ColA VARCHAR(100) NOT NULL); INSERT INTO TabA VALUES ('Test1'); SELECT * FROM TabA; EXEC sp_Test1; SELECT count(*) FROM TabA; DELETE TabA; INSERT INTO TabA VALUES ('Test1'); SELECT * FROM TabA; EXEC sp_Test1; SELECT count(*) FROM TabA;
sql-server-2008cursor
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Stephan Utiger The way your procedure is written allows the cursor to never bail out of the loop so there is no way that this is the behaviour you want. If the table already exists and has some records in it then fetch of the next row fails after the code looped through 392 times, so if you leave the table alone and just keep executing the procedure, you will get 392 more rows inserted into your TabA and then the next fetch will fail with the status = -1. The most dangerous part of the code is that you have a cursor for select from the table and then you insert a row into the same table inside of the cursor loop which is fed from the select from this table. This is very unsafe, and should be avoided if at all possible. Maybe you can shed some light on the actual problem you need to resolve and someone can help you to come up with the solution, which is very probably going to be possible without the need to wheel out the slow cursor loop. SELECT is already a (very fast) loop, so it is very seldom that the cursor loops are actually needed in T-SQL, the odds are high that almost anything can be done with a set-based solution.
2 Likes 2 ·

1 Answer

· Write an Answer
Utiger avatar image
Utiger answered
It's just a test to understand how a FAST_FORWARD CURSOR works, because I had a similar problem with such a type of cursor! I still don't understand why it loops through 1589737 times when the procedure is executed the first time after drop the table and only 392 times when the procedure is called for the second time. In both cases there is only one record in the table "TabA" before the procedure is executed. I would expect that the behaviour is the same for both executions and I would also expect that the loop ends only when the text which is inserted in "ColA" will be more than 100 characters.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Stephan Utiger Actually, the text inserted in ColA will never reach 100 characters. The maximum number digits of the integer is limited to 9 characters, so if the loop never ends then once the value of your int variable reaches 2147483647, the next attempt to increase it by 1 will lead to **Arithmetic overflow error converting expression to data type int** at which time the value will remain equal to 2147483647, which will be inserted to the TabA anyway, and just keep on going. Fortunately, the engine stops this insanity well before that happens, but if you want to see how it works, alter you procedure to have the int variable declared as smallint and you will see what I mean. Depending on the value of the **xact\_abort**, the loop will either terminate abruptly or keep on inserting the same value "Test32767" to your table until the loop dies after 1589737 iterations if you start from fresh (dropped and recreated) table. In any case, nothing resembling the code should ever happen to the SQL Server. The cursor loop should never have to work with records which it fetched but then the new records are inserted to where the cursor points to. This is just far beyond wrong IMHO.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.