Open and Close Cursors Inside or Outside a Transaction and How to Close a Cursor if a Transaction Fails
I am writing a stored procedure in SQL Server 2012 that uses a cursor for reading and a transaction inside a TRY CATCH
block. Basically, my questions are as follows:
- Should I declare my cursor inside the
TRY CATCH
block? If yes, should I declare the cursor before or after theBEGIN TRANSACTION
statement? - Should I open the cursor before or after the
BEGIN TRANSACTION
statement? - Should I close and deallocate the cursor before or after the
COMMIT TRANSACTION
statement? - Should I close and deallocate the cursor before or after the
ROLLBACK TRANSACTION
statement if something fails?
Sample T-SQL Code:
DECLARE @ColumnID AS INT;
DECLARE @ColumnName AS VARCHAR(20);
DECLARE @ColumnValue AS FLOAT;
-- Should I declare my cursor inside the TRY CATCH block?
-- If yes, should I declare the cursor before or after the BEGIN TRANSACTION statement?
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
a.ColumnID,
a.ColumnName,
a.ColumnValue
FROM
MyTable a;
BEGIN TRY
-- Should I open the cursor before or after the BEGIN TRANSACTION statement?
BEGIN TRANSACTION myTransaction;
OPEN myCursor;
FETCH NEXT FROM myCursor INTO @ColumnID, @ColumnName, @ColumnValue;
WHILE @@FETCH_STATUS = 0 BEGIN
IF (@ColumnName IS NULL) BEGIN
UPDATE
MyTable
SET
@ColumnValue = NULL
WHERE
ColumnID = @ColumnID;
END;
FETCH NEXT FROM myCursor INTO @ColumnID, @ColumnName, @ColumnValue;
END;
-- Should I close and deallocate the cursor before or after the COMMIT TRANSACTION statement?
CLOSE myCursor;
DEALLOCATE myCursor;
COMMIT TRANSACTION myTransaction;
END TRY
BEGIN CATCH
-- Should I close and deallocate the cursor before or after the ROLLBACK TRANSACTION statement:
IF CURSOR_STATUS('local', 'myCursor') = 1 BEGIN
CLOSE myCursor;
DEALLOCATE myCursor;
END;
ROLLBACK TRANSACTION myTransaction;
END CATCH;
I would declare and open the cursor before the BEGIN TRY
and then close and deallocate it after the END CATCH
to minimize the amount of time you're spending in the transaction. This also means you don't need to write the close/deallocate statements twice.
My second choice would be to declare and open the cursor inside the BEGIN TRANSACTION
and then close and deallocate before the ROLLBACK
. I'm sure others will prefer this style.
These ways the cursor is either entirely outside the try/catch and transaction or entirely contained within them. Doing it otherwise feels like crossing scopes to me, but would certainly still work. I think this question is mainly an issue of style