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)