Cursors I think everyone uses them at some-point, in general cursors get a bad wrap they are a tool like any other, use it when its appropriate.  I’m not going to go into the pros and cons of cursors.  The key here is to CLOSE the cursor.  People do one of two things, either rely on the closing of a connection and/or the script/procedure to handle removing the cursor or they properly close and deallocate the cursor at the end of code block.  The later is the appropriate way to handle it.  What if you are writing code with a try catch block that has a cursor setup and break down inside it, don’t forget to close and deallocate the cursor in the catch block.  What you say?  You don’t know if the the cursor is open or not or if it has even been allocated?  You’re right depending on your code and your try/catch block you can’t assume the status of the cursor. So here is some code to help manage the close and removal of the the cursor no matter what the situation.

/*Cursor Quick Check*/
declare @Cursor_Status smallInt;
set @Cursor_Status = CURSOR_STATUS('global','looper');
if @Cursor_Status>-1
BEGIN
	close looper;
	deallocate looper;
END
ELSE IF @Cursor_Status in (-1,-2)
	deallocate looper;

For those of you who would want a little bit more in-depth  example here is a script that declares a cursor inside a try/catch block, causes a divide by zero error and then checks the cursor status to properly close the cursor without error.  Notice that the try portion of the try/catch block would close the cursor if the divide by zero error did not take place.

/* Cursor Example Code*/
BEGIN TRY
	declare looper cursor for
	select name from sys.tables
	open looper;	
 
	select 1/0 as [Cause Error];
 
	close looper;
	deallocate looper;
END TRY
BEGIN CATCH
	select ERROR_MESSAGE();
	declare @Cursor_Status smallInt;
	set @Cursor_Status = CURSOR_STATUS('global','looper');
	select @Cursor_Status as [Cursor_Status]; 
 
	if @Cursor_Status>-1
	BEGIN
		close looper;
		deallocate looper;
	END
	ELSE IF @Cursor_Status in (-1,-2)
		deallocate looper;
 
	select CURSOR_STATUS('global','looper')  as [Cursor_Status];
END CATCH