CODEWITHSUNDEEP
sql-server
codingguy3000
codingguy3000

Reputation: 2835

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

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

Upvotes: 8

Views: 20534

Answers (5)

kemiller2002
kemiller2002

Reputation: 115490

Why don't you just use an if statement:

IF 'condition exists that I want to update'
BEGIN

....

END

Fetch Next

Upvotes: 8

KM.
KM.

Reputation: 103587

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>

Upvotes: 12

Jon Galloway
Jon Galloway

Reputation: 53115

I've never found a SQL cursor I couldn't remove with a little thought, and the performance gains are usually huge. Is there a reason you can't select only the rows you want to operate on, rather than doing row by row processing?

Upvotes: -1

Philip Kelley
Philip Kelley

Reputation: 40309

The cheap answer is to insert an IF block: begin loop / IF row is good BEGIN ... END / end loop.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

It might be easier to answer this if we knew what the cursor code looked like. Perhaps you can put in an if statement that checks for the condition and only does something if the condition is not present.

Upvotes: 0

Related Questions