Any way to have a cursor variable reference an *existing* cursor?

(SQL Server 2000, SP3a)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure
A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name o
f the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it f
rom stored
procedure B in a cursor variable.
I had hoped that maybe I could somehow divine the "handle" from sp_describe_
cursor, but I
can't seem to "set" my cursor variable from any of this information. Someth
ing like (once
I retrieve the cursor_handle or something):
set @.MyCursor = cursor @.cursor_handle
Does anyone know of any mechanism I can use to accomplish this?
Thanks!
John PetersonI had hoped that something like this would work:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor =
@.Cursor
output
execute('deallocate ' + @.CursorName)
But, alas, I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly some
thing with the
[sp_executesql] line. No amount of massaging will get this to work -- my gu
ess is that
the structure of [sp_executesql] won't permit a cursor variable to be handle
d. :-(
I'd appreciate any other suggestions!
Regards,
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uxudcE99DHA.3488@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello, all!
> I have an open global cursor that is created dynamically by stored procedure A. I
'd
like
> to reference this cursor from stored procedure B. I know the dynamic name of the[
/color]
cursor,
> but I know of no way to get a "handle" of this cursor so that I can use it
from stored
> procedure B in a cursor variable.
> I had hoped that maybe I could somehow divine the "handle" from sp_describe_cursor
, but
I
> can't seem to "set" my cursor variable from any of this information. Something li
ke
(once
> I retrieve the cursor_handle or something):
> set @.MyCursor = cursor @.cursor_handle
> Does anyone know of any mechanism I can use to accomplish this?
> Thanks!
> John Peterson
>