T-sql Cursor, what will happen in case of error?

Question

Hi

What will happen if an error occurred during a- Declaring Cursor b- Fetch data

What will happen if error occurred before Cursor is closed, does it close automatically?

When I use a Cursor, What is the best practice for handling errors?

Thanks

Answer 1

When an error occurs after declaring the cursor and the batch terminates, the cursor will remain open. The cursor will be closed after the connection is closed.

If you can capture the error, it is good practice to close the cursor as part of the error handling.

Answer 2

Assuming you're using SQL 2005 or greater, the easiest way I have found to handle this situation is to enclose the cursor statement in a TRY...CATCH block, then assume that the cursor is still open in the CATCH.

The CLOSE and DEALLOCATE statements are contained inside a TRY...CATCH of their own, since an attempt to close a cursor which is already closed generates an error too.

Something like

BEGIN TRY

...cursor statement

END TRY
BEGIN CATCH

    BEGIN TRY
        CLOSE cursor
        DEALLOCATE cursor
    END TRY
    BEGIN CATCH
        -- silently catch the error if the cursor is already closed
    END CATCH

    ...other error handling

END CATCH