SQL Server 2008: BEGIN TRY and BEGIN TRANSACTION in CURSOR

Question

I am trying to loop through a number of records and process each of them. The cursor should continue looping through the records even if an ERROR occurs in processing, but at the end of the batch, I would like to see the error messages of ALL records which produced errors.

I have the following code:

FOR SELECT....

    OPEN cur
    FETCH next FROM cur INTO
    @some_variables
    WHILE @@fetch_status = 0
    BEGIN

    BEGIN TRY
      BEGIN TRANSACTION
        ...process the record...
      COMMIT
    END TRY

   BEGIN CATCH
   ROLLBACK

     RAISERROR ('%s',16, 1, @variable_containing_error)

    END CATCH
    END

            FETCH next FROM cur INTO
           @some_variables


    CLOSE cur
    DEALLOCATE cur

If I process two records, both with errors, I only get the first error message and not the second records error message.

I am not sure if I am using the BEGIN TRY and TRANSACTION commands properly.

Can someone please help?

Answer 1

You should try with out the transaction when you do rollback it is stopping your code try this:

While @@Fetch_Status=0
Begin
    Begin Try
        <You code goes here>        
    End Try
    Begin Catch
        RAISERROR ('%s',16, 1, @variable_containin_gerror)
    End Catch

    FETCH next FROM cur INTO
       @some_variables
End

Making this code I see an error you have:

You are closing your cursor before doing the fetch Next from. Also, you should try put the last end after the last fetch next from.