testing for cursor

[Bill Crawley]

Hi All,

I have a cursor in a try catch block of SQLServer 2005.

At the end of the try block as long as it doesn't error, I close and deallocate the cursor.

However, should it fail and drop into the catch block there is a possibility that the cursor is still open.

In my catch block, how can I test for the existance of the cursor so that if it exist's I can close and deallocate it.

[Alsvha]

Take a look at the CURSOR_STATUS function

[Bill Crawley]

i've now got :

if cursor_status('variable','percentage_cursor') >= 0 --check the cursors not open
BEGIN
CLOSE percentage_cursor
DEALLOCATE percentage_cursor
END

In my try catch block, but it still leaves the cursor open if it fails.

[Alsvha]

Try 'global' instead of 'variable'