TSQL Cursor, how to check whether it is already declared and thereby free

Question

How can I make sure that I release the cursor if it already exists before I try to 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 figure out how to do this for the cursor, for example

 -- 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 do this to clear my script before it starts

The 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 a maintennance script. Our highly customizable database clients can have many tables, and the cursor is used to start statistical analysis of the tables - different things happen depending on the types of tables. Basically a lot of dynamic sql. If the script crashes, I would like to repeat the job without worrying about manual intervention. There is only one level of coverage.

Like everyone else, I'm glad to replace the cursors with the given operations. This is what the cursor loops do:

• build sql for reorg / rebuild indexes (initially there was a manual sql for determining DDL to run, and then DDL was released)
• analysis of data distribution and errors in different tables
• find errors in the logs and find the corresponding tables and capture this data (initially there was a manual sql to determine the places where errors were cut out somewhere and templates were inserted to search for errors depending on the types of errors)

Answer 1

You can declare the cursor as a variable, then it will be closed and automatically released when it goes beyond the bounds. An example of using this in combination with dynamic SQL is 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

When you create a CURSOR variable and then use SET to define it, it really automatically releases it when the variable goes out of scope (i.e. the end of the package or subprocess), this construct is also completely unnecessary.

Assuming that you do not pass the cursor to the various nested levels of the process or need it to survive between batches, then the simplest approach (and probably the best overall) is to declare the cursor as LOCAL . While unspecified is configured between LOCAL and GLOBAL , it is set by default that the cursors are GLOBAL if they are not specified. The specified code does not have the LOCAL keyword, so we can assume that the cursor is GLOBAL , so this problem of the need for cleaning before launch even arose. So, just add the LOCAL keyword to the cursor declaration.

Now, for those cases when you really need the GLOBAL cursor, and you need to check first to make sure it has not been declared, there are two easy ways to check this:

1. select from 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, then it is open or closed, and if it is open, then if there are 0 or more than 0 lines.

I also checked on SQL Server 2005, SP4 and found that all three of the above elements also behaved there.