Skip to main content

Exit a T-SQL Cursor When Condition is met

Have you ever wanted to exit from a cursor when a condition is met? I wanted to do it. So this is how I did it.

DECLARE @Field1 AS INT
DECLARE @Field2 AS INT

DECLARE CursorName CURSOR READ_ONLY
FOR

SELECT
Field1,
Field2
FROM TableName

OPEN CursorName

FETCH NEXT FROM CursorName
INTO @Field1, @Field2

WHILE @@FETCH_STATUS = 0
BEGIN

IF @Field1 = 1
BEGIN
GOTO ENDCURSOR
END

FETCH NEXT FROM CursorName
INTO @Field1, @Field2

END

ENDCURSOR:
CLOSE CursorName
DEALLOCATE CursorName

I have set my fonts to bold where you want to notice.

So that's all I hope you will get something out of it and it is true that this is not a big deal. :)
Labels:

Comments

Ghost said…
It's strange that T-SQL doesn't have a break statement. GOTO statements are pretty old stuff now.
December 2, 2009 at 12:52 AM
Le Dan said…
Why not just declare a variable as BIT and make it a further condition for the while-loop like this:

DECLARE @Field1 AS INT
DECLARE @Field2 AS INT
DECLARE @StopWhile BIT

DECLARE CursorName CURSOR READ_ONLY
FOR

SELECT
Field1,
Field2
FROM TableName

OPEN CursorName

FETCH NEXT FROM CursorName
INTO @Field1, @Field2

WHILE @@FETCH_STATUS = 0 AND @StopWhile = 0
BEGIN

IF @Field1 = 1
BEGIN
SET @StopWhile = 1
END

FETCH NEXT FROM CursorName
INTO @Field1, @Field2

END

CLOSE CursorName
DEALLOCATE CursorName

Just another possibility :)
January 13, 2010 at 8:23 AM
Oshadha said…
Yes this bit variable thing will be a effective way as I feel. Thanks Le Dan.
February 15, 2010 at 9:15 PM
Post a Comment