Click here to Skip to main content
15,868,016 members
Articles / Database Development / SQL Server
Tip/Trick

Always close your cursor

Rate me:
Please Sign up or sign in to vote.
4.50/5 (2 votes)
13 Oct 2011CPOL 32.1K   2  
When cursors are inside transaction, it is sometimes easy to forget to close the 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:

SQL
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) The Code Project
United States United States
Elina joined the Code Project team as a software developer in order to make the site run even smoother than it is now. She will also take care of making wishes and promises on the site improvements come to the light sooner than later. When not at work, she enjoys being with her family and wishes there will be at least 30 hours in a day Smile | :)

Comments and Discussions

 
-- There are no messages in this forum --