Always close your cursor

If in your stored procedure you defined a cursor, it is sometimes easy to forget to close it. Below are examples on how to ensure cursors are closed:

BEGIN TRAN

DECLARE @CurrentId int
DECLARE @IdCursor CURSOR -- set cursor as a a vraible to ensure not global scope
SET @IdCursor = CURSOR FOR SELECT Id FROM MyTable
            
 OPEN @IdCursor
        
FETCH NEXT FROM @IdCursor INTO @CurrentId
WHILE (@@FETCH_STATUS) = 0
    BEGIN   
        
    -- do work               
     IF @@error <> 0  GOTO err
                    
    FETCH NEXT FROM  @IdCursor INTO @CurrentId
END -- end cursor

CLOSE @IdCursor
DEALLOCATE @IdCursor	
			
   
COMMIT TRAN
RETURN 0 -- success

err:

ROLLBACK TRAN 
-- ensure cursor is closed
DECLARE @CursorStatus int
SET  @CursorStatus = CURSOR_STATUS('variable','@IdCursor')
IF (@CursorStatus)  > 0 -- cursor is opened
	CLOSE @IdCursor
IF (@CursorStatus <> -2) -- this is status after dealocate
  DEALLOCATE @IdCursor

RETURN 1   -- error