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
ليست هناك تعليقات:
إرسال تعليق