Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to close and deallocate cursor in CATCH Block?

Author  Topic 

aex
Yak Posting Veteran

60 Posts

Posted - 2009-05-02 : 02:53:40
[code]
BEGIN TRY
DECLARE Cur CURSOR FAST_FORWARD FOR
SELECT Id FROM Staff
OPEN Cur
FETCH NEXT FROM Cur INTO @Id

WHILE (@@FETCH_STATUS = 0)
BEGIN
-- processing..

FETCH NEXT FROM Cur INTO @Id
END
CLOSE Cur
DEALLOCATE Cur
END TRY
BEGIN CATCH
END CATCH
[/code]

Refer to code above, if there is any error happen in -- processing.. line, the error will be trapped in CATCH block, however, the cursor variable is not allocated. Is there any way to close and deallocate the cursor whenever the abovementioned issue happened?

aex

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-05-03 : 06:54:36
u can write the CLOSE, DEALLOCATE stmts in Catch block also, check this
BEGIN TRY
DECLARE Cur CURSOR FAST_FORWARD FOR
SELECT DISTINCT ID FROM Staff
OPEN Cur
FETCH NEXT FROM Cur INTO @Id

WHILE (@@FETCH_STATUS = 0)
BEGIN
-- processing..
print 'LOOP'

SELECT @ID * 'A'

FETCH NEXT FROM Cur INTO @Id
END
SELECT Cursor_Status('global', 'Cur') AS 'Cur1'

CLOSE Cur
DEALLOCATE Cur
END TRY
BEGIN CATCH
SELECT Cursor_Status('global', 'Cur') AS 'Cur2'
CLOSE Cur
DEALLOCATE Cur
PRINT ERROR_MESSAGE()
SELECT Cursor_Status('global', 'Cur') AS 'Cur2'
END CATCH
If any error occurs then CURSOR is closed in Catch block.

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"
Go to Top of Page
   

- Advertisement -