CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-cursor
David LA
David LA

Reputation: 53

How Can I Skip a row(an iteration) in MSSQL Cursor based on some condition?

How Can I Skip a row(an iteration) in MSSQL Cursor based on some condition, I have a DTS which migrates thousands of records and based on some criteria, some records need not be migrated as they are duplicates and want to skip these records.

Any idea how I can accomplish this in MSSQL Cursor?

Upvotes: 4

Views: 16365

Answers (5)

Ori Shavit
Ori Shavit

Reputation: 41

  1. Use CONTINUE command to skip to the next iteration of a while loop.
    WHILE(@@FETCH_STATUS = 0)
    BEGIN
      FETCH NEXT FROM CURSOR INTO @PARAM
      IF @cond CONTINUE /* Skip */
      /* Do stuff */
    END

  1. Use a Label: and a GOTO to continue execution from another place in the code

       BEGIN
   FETCH NEXT FROM CURSOR INTO @PARAM
   IF @cond GOTO: endOfCursorLabel
   /* Do stuff */
   endOfCursorLabel:
   END

Reference "Control-of-Flow" for more info https://learn.microsoft.com/en-us/sql/t-sql/language-elements/control-of-flow?view=sql-server-ver16

Upvotes: 0

Evander
Evander

Reputation: 41

I know that it I am too late to answer this question but for future seekers.. I believe the correct way to handle this is described in MSDN

your code is using a WHILE Loop (WHILE @@FETCH_STATUS = 0)

To properly exit a while loop you use the BREAK key word

To skip an iteration in your loop you use the CONTINUE keyword

FROM MSDN:

BREAK Causes an exit from the innermost WHILE loop. Any statements that appear after the END keyword, marking the end of the loop, are executed.

CONTINUE Causes the WHILE loop to restart, ignoring any statements after the CONTINUE keyword.

EXAMPLE: -- AFTER declaring and opening a cursor...

FETCH NEXT FROM Curs INTO @ID    
WHILE @@FETCH_STATUS = 0    
BEGIN 
-- do something
IF [condition to skip record] 
CONTINUE            

FETCH NEXT FROM Curs INTO @ID 
  END

Upvotes: 4

vendettamit
vendettamit

Reputation: 14677

I know it's too late to answer this question. But for the sake of other users seeking for a better answer. You can simply skip the record by calling the FETCH NEXT.

WHILE @@FETCH_STATUS = 0

IF @ParameterName = '@RETURN_VALUE' 
    FETCH NEXT FROM @procSchema INTO @ParameterName, @Type;

   .... Rest of the logic goes here

With this approach you don't have to wrap the whole logic in a If statement instead you'll move to the next one record skipping the current record.

Upvotes: 1

AfzalHassen
AfzalHassen

Reputation: 36

I'm making a few assumptions here but you could use the following as a guide: 

    -- create some test data        
    SELECT '                                  ' [Word] INTO #MyTempDataset;
    INSERT INTO #MyTempDataset SELECT 'This';
    INSERT INTO #MyTempDataset SELECT 'is';
    INSERT INTO #MyTempDataset SELECT 'a';
    INSERT INTO #MyTempDataset SELECT 'basic';
    INSERT INTO #MyTempDataset SELECT 'basic';
    INSERT INTO #MyTempDataset SELECT 'test';

    DECLARE @counter INT
    DECLARE @word VARCHAR(50)
    DECLARE myCursor CURSOR
        FOR SELECT  *
            FROM    #MyTempDataset;
        OPEN myCursor
        FETCH NEXT FROM myCursor INTO @word
        WHILE @@FETCH_STATUS = 0
        BEGIN               
            -- check for condition
            SELECT  @counter = Count(*) 
            FROM    #MyTempDataset
            WHERE   word = @word

            IF @counter =1
            BEGIN
                -- process the unique records
                PRINT @word     
            END     

            FETCH NEXT FROM myCursor INTO @word
        END
        CLOSE myCursor;
        DEALLOCATE myCursor;

    DROP TABLE #MyTempDataset;

Upvotes: 2

Almazini
Almazini

Reputation: 1873

I guess the simplest way is to write IF statement inside the cursor. If the condition will be false you will skip records. 

 DECLARE @ID INT
 DECLARE Curs CURSOR FAST_FORWARD
 DECLARE @Cnt INT

 CREATE TABLE ##Duplicates (ID INT, CarColor VARCHAR (50) )

 FOR
        SELECT DISTINCT  CarID
        FROM    dbo.CarPark
        WHERE   CarColour <> 'red'
        ORDER BY CarID

    OPEN Curs

        FETCH NEXT FROM Curs INTO @ID

        WHILE @@FETCH_STATUS = 0

    BEGIN

             INSERT INTO  ##Duplicates
             SELECT CarID, CarColor
             FROM  dbo.CarPark          
             WHERE ID = @ID;

             SET @Cnt = (SELECT Count(*) FROM ##Duplicates WHERE ID = @ID) ;

             IF @Cnt < 2 
             THEN /* Migrate */
             ELSE PRINT 'Duplicate'
             END

        FETCH NEXT FROM Curs INTO @ID 

        END

    CLOSE Curs
    DEALLOCATE Curs;

Upvotes: 4

Related Questions