Error handling in a cursor

  • I am building a script which has a cursor in it.  It is pretty simple, but I am wondering how to use error handling within the cursor.  The code below is basically what I am doing:

    WHILE @@FETCH_STATUS = 0

     BEGIN

     DECLARE @l_error int

      DECLARE @l_filename VARCHAR(255)

       EXECUTE @l_error = sp_xxxxxxxxx @l_filename

     IF (@l_error = 0)

        PRINT 'Applied Script ' + @l_file_name

     ELSE

        PRINT 'FAILED'

      

     FETCH NEXT FROM files INTO @l_file_name

     END

    What I need to have is if an error does happen the cursor stops and gets out of the cursor.

  • Use BREAK to get out of the current WHILE loop.

     

  • Yes, PW is right. BREAK will exit the WHILE loop.

    Quand on parle du loup, on en voit la queue

  • That works perfectly for jumping out of the loop.  I tried putting a print statement with the break to state the table name that the error occured on but it reads as a syntax error.  Any ideas how I might accomplish that?

  • >>I tried putting a print statement with the break to state the table name that the error occured on but it reads as a syntax error.

    Print doesn't handle in-line string concatenation. Build your error msg including the table name into a varchar variable and Print the variable.

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply