Can a cursor variable be assigned to a dynamically named cursor?

(SQL Server 2000, SP3a)
(From a different thread.)
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 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.
The [sp_describe_cursor] returns something called a cursor_handle. Can this be used
somehow to set a cursor variable?
I thought maybe I could do something like this:
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 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 something with the
[sp_executesql] line. No amount of massaging will get this to work -- my guess is that
the structure of [sp_executesql] won't permit a cursor variable to be handled. :-(
Thanks for any help anyone can provide!
John PetersonCursors are usually best avoided because of their performance/resource
implications. Erland has an article on alternative methods for sharing data
between SPs:
http://www.sommarskog.se/share_data.html
For completeness, here's an amended version of your code:
...
SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
...
Now reference the cursor by variable (@.cursor).
--
David Portas
SQL Server MVP
--|||David,
You're right -- I appreciate that cursors aren't wholly performant, but in my case, I'm
writing a management procedure that lends itself well to using cursors.
Thanks for the link on other techniques for sharing data. :-)
I think you solved my issue! From what I can tell, you merely removed the VARYING
keyword. From the stored procedure documentation (which I kind of assumed that
[sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
using a cursor variable. But, it appears not to be the case, and that was the one
combination I *didn't* try!
Thanks so much! :-)
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> Cursors are usually best avoided because of their performance/resource
> implications. Erland has an article on alternative methods for sharing data
> between SPs:
> http://www.sommarskog.se/share_data.html
> For completeness, here's an amended version of your code:
> ...
> SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> ...
> Now reference the cursor by variable (@.cursor).
> --
> David Portas
> SQL Server MVP
> --
>|||Oddly, it seems like there are some things that can't be done with the cursor variable.
For example, I tried:
open @.Cursor
But that doesn't appear to work. Only when the OPEN is in the context of the dynamic SQL
does it seem to open the cursor for the variable.
Additionally:
close @.Cursor
deallocate @.Cursor
Don't appear to work either. If I try and re-run my code snippet, it complains that the
cursor still exists.
Unless the issue is that there are *two* "handles" to the same cursor (the original "By
Name" and the variable) -- and I need to essentially close both handles before the cursor
will be destroyed?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> David,
> You're right -- I appreciate that cursors aren't wholly performant, but in my case, I'm
> writing a management procedure that lends itself well to using cursors.
> Thanks for the link on other techniques for sharing data. :-)
> I think you solved my issue! From what I can tell, you merely removed the VARYING
> keyword. From the stored procedure documentation (which I kind of assumed that
> [sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
> using a cursor variable. But, it appears not to be the case, and that was the one
> combination I *didn't* try!
> Thanks so much! :-)
> John Peterson
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> > Cursors are usually best avoided because of their performance/resource
> > implications. Erland has an article on alternative methods for sharing data
> > between SPs:
> >
> > http://www.sommarskog.se/share_data.html
> >
> > For completeness, here's an amended version of your code:
> > ...
> > SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> > EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> > ...
> >
> > Now reference the cursor by variable (@.cursor).
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
>|||I finally settled on this test bed, which appears to work successfully:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor global forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName + ' open @.Cursor'
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor output', @.Cursor = @.Cursor output
declare @.Name sysname
fetch next from @.Cursor into @.Name
print @.Name
close @.Cursor
deallocate @.Cursor
execute('deallocate ' + @.CursorName)
Thanks again for your help, David! I was dispairing that a solution could be found. :-)
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eKHgxlI%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Oddly, it seems like there are some things that can't be done with the cursor variable.
> For example, I tried:
> open @.Cursor
> But that doesn't appear to work. Only when the OPEN is in the context of the dynamic
SQL
> does it seem to open the cursor for the variable.
> Additionally:
> close @.Cursor
> deallocate @.Cursor
> Don't appear to work either. If I try and re-run my code snippet, it complains that the
> cursor still exists.
> Unless the issue is that there are *two* "handles" to the same cursor (the original "By
> Name" and the variable) -- and I need to essentially close both handles before the
cursor
> will be destroyed?
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> > David,
> >
> > You're right -- I appreciate that cursors aren't wholly performant, but in my case,
I'm
> > writing a management procedure that lends itself well to using cursors.
> >
> > Thanks for the link on other techniques for sharing data. :-)
> >
> > I think you solved my issue! From what I can tell, you merely removed the VARYING
> > keyword. From the stored procedure documentation (which I kind of assumed that
> > [sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
> > using a cursor variable. But, it appears not to be the case, and that was the one
> > combination I *didn't* try!
> >
> > Thanks so much! :-)
> >
> > John Peterson
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> > > Cursors are usually best avoided because of their performance/resource
> > > implications. Erland has an article on alternative methods for sharing data
> > > between SPs:
> > >
> > > http://www.sommarskog.se/share_data.html
> > >
> > > For completeness, here's an amended version of your code:
> > > ...
> > > SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> > > EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> > > ...
> > >
> > > Now reference the cursor by variable (@.cursor).
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> > >
> >
> >
>