How to check, if a cursor already exists ?

[calvinsinger]

I want to be able put a check in a SQL stored procedure, to check, if a cursor already exists. It it exists, I want to deallocate it and only then create a new one.

How can I check (how to write the code ?) if a cursor by given name already exists. Thanks

Background...
The reason I get an error telling me that the cursor already exists is because sometimes the stored procedure which creates the cursor and then uses it, aborts mid-way, due to a data error. It then exists before it can reach the code portion which deallocates the the cursor. So the cursor is left allocated.

 

[mrdenny]

Take a look at CURSOR_STATUS in BOL.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[calvinsinger]

Thank you
Calvin

 

[SQLSister]

If you use transaction processing, you can on error, not only rollback the transactions but close and deallocate the cursor.

Of course, in general you should avoid using a cursor. Cursors are notorioulsy inefficent and can almost always be avoided.

Questions about posting. See faq183-874