Click here to Skip to main content
Click here to Skip to main content

Always close your cursor

, 13 Oct 2011
Rate this:
Please Sign up or sign in to vote.
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:
 
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)

About the Author

Elina Blank
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 --
| Advertise | Privacy | Mobile
Web02 | 2.8.140709.1 | Last Updated 13 Oct 2011
Article Copyright 2011 by Elina Blank
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid