Sql-server – TSQL Cursor how to check if already declared and thus deallocate

cursorsql servertsql

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)

Best Answer

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
Related Topic