Menu
EXEC SQL CLOSE cursor-name END-EXEC. If you processed the rows of a result table and you do not want to use the cursor again, you can let the system close the cursor. The system automatically closes the cursor when: A COMMIT without HOLD statement is issued and the cursor is not declared using the WITH HOLD clause.
Not deallocating AFAIK only has to do with performance. The aforementioned resources will remain allocated and thus have a negative effect on server performance.
DEALLOCATE removes the association between a cursor and the cursor name or cursor variable. If a name or variable is the last one referencing the cursor, the cursor is deallocated and any resources used by the cursor are freed. Scroll locks used to protect the isolation of fetches are freed at DEALLOCATE .
The COMMIT WORK and ROLLBACK WORK statements close all cursors except those that are declared with a hold. It is better to close all cursors explicitly, however. For Select or Function cursors, this action simply makes the intent of the program clear.
Let's say I've got a trigger like this:
CREATE TRIGGER trigger1
ON [dbo].[table1]
AFTER UPDATE
AS
BEGIN
--declare some vars
DECLARE @Col1 SMALLINT
DECLARE @Col1 TINYINT
--declare cursor
DECLARE Cursor1 CURSOR FOR
SELECT Col1, Col2 FROM INSERTED
--do the job
OPEN Cursor1
FETCH NEXT FROM Cursor1 INTO @Col1, @Col2
WHILE @@FETCH_STATUS = 0
BEGIN
IF ...something...
BEGIN
EXEC myProc1 @param1 = @Col1, @Param2 = @Col2
END
ELSE
IF ...something else...
BEGIN
EXEC myProc2 @param1 = @Col1, @Param2 = @Col2
END
FETCH NEXT FROM Cursor1 INTO @Col1, @Col2
END
--clean it up
CLOSE Cursor1
DEALLOCATE Cursor1
END
I want to be sure that Cursor1 is always closed and deallocated. Even myProc1 or myProc2 fails.
Shall I use try/catch block?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With