June 2009 Archives

Closing A Cursor in SQL Catch

June 29, 2009

Problem: In a T-SQL script, an exception occurs while a cursor is open, resulting in the cursor never being closed. But, the exception handling wraps the entire script, not just the cursor, so there is no guarantee that the cursor will be open if/when the CATCH statement is reached.

Solution: query the sys.syscursors view to see if the cursor(s) in question is still open:

BEGIN CATCH
     ...

     IF EXISTS (SELECT 1 FROM sys.syscursors WHERE cursor_name = 'MyCursor')
     BEGIN
	DEALLOCATE MyCursor
     END
     
     ...
END CATCH

Update 7/14/09

I just tried to deploy this to a development environment, rather than my own computer. There it was running as a user with restricted access. I received the following error:The SELECT permission was denied on the object 'syscursors', database 'mssqlsystemresource', schema 'sys'."

This was easily remedied when I finally discovered the CURSOR_STATUS function:

DECLARE @cursorstatus int;
SELECT @cursorstatus = cursor_status('global','MyCursor')
IF @cursorstatus > -2
BEGIN
	DEALLOCATE MyCursor
END

Think For One ... Second

June 20, 2009

What's wrong with this code? There are unnecessary lines. So? Why care about unnecessary lines? Because it shows that the programmer was not really thinking about what he was doing.

        MyObject obj = someList.Find(delegate(MyObject test)
        {
            if (test.Id.Equals(packageId))
            {
                return true;
            }
            else
            {
                return false;
            }
        });

About this Archive

This page is an archive of entries from June 2009 listed from newest to oldest.

May 2009 is the previous archive.

August 2009 is the next archive.

Find recent content on the main index or look in the archives to find all content.

Categories

Pages

OpenID accepted here Learn more about OpenID
Powered by Movable Type 5.12