CODEWITHSUNDEEP
sql-servert-sqldatabase-cursor
Preet Sangha
Preet Sangha

Reputation: 65546

TSQL Cursor how to check if already declared and thus deallocate

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:

Upvotes: 7

Views: 7211

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48874

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.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453707

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

Upvotes: 7

Related Questions