TSQL Cursor how to check if already declared and thus deallocate

Question

How can I make sure that I deallocate a cursor if it already exists before I try and open it again?

For a table I can use something like:

if exists (select top 1 from tempdb.sys.tables where name = '##tmpTable') 
    drop table ##tmpTable;  
... then I can recreate my ##tmpTable 

But I can't work out how to do it for a cursor like

-- First clean up if already exists..
  .....                                  <----- what goes here???

-- Declare and use a cursor
DECLARE  someCursorName CURSOR 
 FOR 
  select something from somewhere
 FOR READ ONLY

I'm doing this to ensure that my script cleans up before it starts work

Best I can come up with is :

begin try DEALLOCATE someCursorName ; end try begin catch end catch

Is this a good practice?

EDIT: This is maintennance script. In our heavily customer customised databases there can be many tables and the cursor is used to run statistical analyses across the tables - depending on the types of tables different things happen. Basically lots of dynamic sql. If the script fails I'd like to be able to repeat the job without worrying about manual intervention. There is only one level of scope here.

Like all things I'm happy to replace the cursors with set operations. These are the things that the cursors loops do:

• construct sql to reorg/rebuild indexes (orginally there was manual sql to determine the DDL to run, and then the DDL was issued)
• analyse data spreads and errors in different tables
• find errors in logs and look up appropriate tables and grab that data (orginally there was manual sql to determine the places where errors where and then cut and paste template(s) to look up the errors dependant upon types of error)

Answer 1

You can declare the Cursor as a variable then it will be closed and deallocated automatically when it goes out of scope. Example of using this in conjunction with dynamic SQL below.

DECLARE @C1 AS CURSOR;

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT name
    FROM   master..spt_values
    WHERE  name <> ''
    ORDER  BY name;

OPEN @C1;

EXEC sp_executesql N'
DECLARE @name VARCHAR(50)
FETCH NEXT FROM @C1 INTO @name;

WHILE @@FETCH_STATUS = 0
  BEGIN
      PRINT @name

      FETCH NEXT FROM @C1 INTO @name;
  END 
', N'@C1 CURSOR', @C1 = @C1

Answer 2

While creating a CURSOR variable and then using SET to define it will indeed automatically deallocate it when the variable goes out of scope (i.e. end of the batch or sub-process), that construct is also entirely unnecessary.

Assuming that you aren't passing the cursor itself around to various nested levels of the process, or need it to survive between batches, then the simplest approach (and probably best overall) is to declare the cursor as LOCAL. While unspecified is configurable between LOCAL and GLOBAL, the default setting is that cursors are GLOBAL if not specified. In the code in the question, there is no LOCAL keyword so we can assume that the cursor is GLOBAL which is why this issue of needing to clean up a prior run even came up. So, just add the LOCAL keyword to the cursor declaration.

Now, for those cases where you actually want a GLOBAL cursor and so need to check first to make sure that it hasn't already been declared, there are two easy ways to test for this:

1. select from the sys.dm_exec_cursors DMF (similar to using sys.tables for tables)
2. use the CURSOR_STATUS function which can tell you if it exists, and if so, if it's open or closed, and if open, then if there are 0 or more than 0 rows.

I also tested on SQL Server 2005, SP4 and found that all three items noted above behaved the same way there as well.