Close cursor in another procedure

Hi,
I declared a cursor in an Insert trigger. In this trigger I called a sp. Can
I close this cursor in the sp?
----
CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
...
DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTED
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @.MyVariable
WHILE @.@.FETCH_STATUS = 0
BEGIN
...
IF @.@.ERROR <> 0
BEGIN
EXEC MY_ERROR_SP
RETURN
END
...
END
...
----
CREATE MY_ERROR_SP AS
...
IF @.@.TRANCOUNT > 0
ROLLBACK
CLOSE MyCursor --CAN I CLOSE cURSOR HERE?
DEALLOCATE MyCursor --CAN I DEALLOCATE cURSOR HERE?
...
----Why have you put a cursor in a trigger? Cursors are rarely a good idea
and triggers are absolutely the last place you should use them. Updates
are set-based so triggers should be too.
The answer to your question is no, because your cursor is local. The
better answer is rewrite your trigger.
David Portas
SQL Server MVP
--|||Cursors by default hv global scope. Therefore, it is possible to create them
in 1 SP1 & close it in SP2 called from SP1.
Rakesh
"Tod" wrote:

> Hi,
> I declared a cursor in an Insert trigger. In this trigger I called a sp. C
an
> I close this cursor in the sp?
> ----
> CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
> ...
> DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTE
D
> OPEN MyCursor
> FETCH NEXT FROM MyCursor INTO @.MyVariable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> ...
> IF @.@.ERROR <> 0
> BEGIN
> EXEC MY_ERROR_SP
> RETURN
> END
> ...
> END
> ...
> ----
> CREATE MY_ERROR_SP AS
> ...
> IF @.@.TRANCOUNT > 0
> ROLLBACK
> CLOSE MyCursor --CAN I CLOSE cURSOR HERE?
> DEALLOCATE MyCursor --CAN I DEALLOCATE cURSOR HERE?
> ...
> ----
>
>|||David is right.. since ur cursor is a local one, u will not be able to close
.
"Rakesh" wrote:
> Cursors by default hv global scope. Therefore, it is possible to create th
em
> in 1 SP1 & close it in SP2 called from SP1.
> Rakesh
> "Tod" wrote:
>