How to Exit a T-SQL Cursor When Condition is met

If you are using cursor and you want to stop the cursor when it meet specific condition, to achieve that we can use additional condition in the WHILE statement, along with @@FETCH_STATUS the new condition will control when the loop has to stop.

In the following example @StopLoop parameter used to control the cursor , this cursor will stop in two conditions, this first one when @@FETCH_STATUS<>0 and this mean all records has been processed and the second condition when @StopLoop<>1.

   

DECLARE @Param1 AS INT

DECLARE @Param2 AS VARCHAR

DECLARE @Param3 AS VARCHAR

DECLARE @StopLoop AS INT

SET @StopLoop=0

DECLARE NEWLOOP CURSOR READ_ONLY

FOR

SELECT

Field1,

Field2,

Field3

FROM Table

OPEN NEWLOOP

FETCH NEXT FROM NEWLOOP INTO @Param1, @Param2,@Param3

WHILE @@FETCH_STATUS = 0 AND @StopLoop=0

BEGIN

{…… SQL Statement to process the data …...}

{…… SQL Statement to process the data …...}

IF @Param1 = 1 AND @Param2='stop'

BEGIN

SET @StopLoop = 1

END

FETCH NEXT FROM NEWLOOP INTO @Param1, @Param2,@Param3

END

CLOSE NEWLOOP

DEALLOCATE NEWLOOP

http://www.dynamicssol.com/ShowArticle.aspx?ID=10