SQL Server How Do You Iterate Though a Cursor Loop Once When a Specific Condition Occurs

Question

I have a SQL Server cursor. I want to skip one iteration of the loop when a specific condition occurs. Break takes you out of the cursor loop and continue does not appear to be doing anything.

Is there a command that says "hey this record is no good so let go ahead and skip it and work on the next one".

By the way I know cursors are evil like drivers who go 43 MPH in the passing lane, but as often happens in software I'm stuck with it.

Thanks

Answer 1

if you code your loop with the fetch at the bottom (with the initial fetch before the loop) continue will just jump you to the top, and process the same row again. you could use a GOTO to jump to the fetch part at the bottom or restructure the loop to fetch at the top and the cointinue will work.

you could modify your loop to use GOTO...

...
...
if <condition>
BEGIN
    GOTO Fetch_Next
END
....
....
Fetch_Next:
FETCH NEXT FROM ...

Here is some sample code for only one fetch at top of loop, continue will work:

DECLARE <cursor_name> CURSOR FOR
    SELECT
    FROM
    WHERE
    FOR READ ONLY

--populate and allocate resources to the cursor
OPEN <cursor_name>

--process each row
WHILE 1=1
BEGIN

    FETCH NEXT FROM <cursor_name>
        INTO @a, @b, @c

    --finished fetching all rows?
    IF @@FETCH_STATUS <> 0
    BEGIN --YES, all done fetching
        --exit the loop
        BREAK
    END --IF finished fetching

    --do something here--
    --do something here--

    IF <your condition>
    BEGIN
        CONTINUE  -- fetch next row
    END

    --do something here--
    --do something here--

END --WHILE

--close and free the cursor's resources
CLOSE <cursor_name>
DEALLOCATE <cursor_name>

Answer 2

Why don't you just use an if statement:

IF 'condition exists that I want to update'
BEGIN

....

END

Fetch Next