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 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.
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 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 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. :-(
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
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 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 Peterson
>

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
>

Any way to avoid using a cursor and a script on this one?

A while back a db expert I was talking to expressed the opinion that
cursors were overused, mostly by programmers who were thinking like
programmers instead of db people.
I have a task to do and I'm not sure I can do it without a cursor.
I'm curious to see if anyone can think of a way around it.
In a nutshell I want to query the database to find all tables that have
a particular field( "comment"), include only results where that field
has one of 20 substrings in the content, and then print it all out.
So I have
(substring1, substring2 substring3 ...substring20)
and I would like to get output like
table_name comment substring
-- -- --
If I can find another way than using cursors or brute force ( many
cut-n-pasted tsql statements ) I would be grateful.
Thanks in advance for any thoughts
SteveWell this sounds like a one of a kind request. If it isn't you have some
serious design flaws. A cursor is useful for something like that where you
need to navigate multiple objects dynamically. What you should not use a
cursor for are things that can get the results via a SET based approach. For
instance you would not create a cursor to navigate each row of the table to
search for yoru string. You would do that in a SET based fashion.
Andrew J. Kelly SQL MVP
"Steve" <stevesusenet@.yahoo.com> wrote in message
news:1139531021.868263.148350@.g47g2000cwa.googlegroups.com...
>A while back a db expert I was talking to expressed the opinion that
> cursors were overused, mostly by programmers who were thinking like
> programmers instead of db people.
> I have a task to do and I'm not sure I can do it without a cursor.
> I'm curious to see if anyone can think of a way around it.
> In a nutshell I want to query the database to find all tables that have
> a particular field( "comment"), include only results where that field
> has one of 20 substrings in the content, and then print it all out.
> So I have
> (substring1, substring2 substring3 ...substring20)
> and I would like to get output like
> table_name comment substring
> -- -- --
> If I can find another way than using cursors or brute force ( many
> cut-n-pasted tsql statements ) I would be grateful.
> Thanks in advance for any thoughts
> Steve
>|||Hello, Steve
Let's take it one step at a time. First, we need a list of tables that
have a column named "comment":
SELECT o.name FROM syscolumns c INNER JOIN sysobjects o ON c.id=o.id
WHERE o.xtype='U' AND c.name='comment'
Then, for each of the above tables, we would want to do something like
this:
SELECT 'Some table' as table_name, comment, sub_string
FROM [Some table] t INNER JOIN (
SELECT 'substring1' sub_string
UNION SELECT 'substring2'
UNION SELECT 'substring3'
/* ... */
) x ON t.comment LIKE '%'+sub_string+'%'
Instead of writing the substrings like above, we can use a temporary
table to store them:
CREATE TABLE #substrings (sub_string varchar(50) PRIMARY KEY)
INSERT INTO #substrings VALUES ('substring1')
INSERT INTO #substrings VALUES ('substring2')
INSERT INTO #substrings VALUES ('substring3')
/* ... */
Now we can generate the SELECT-s for all the tables, using the
following query:
SELECT '
SELECT '''+o.name+''' as table_name, comment, sub_string
FROM ['+o.name+'] t INNER JOIN #substrings x
ON t.comment LIKE ''%''+sub_string+''%''
' FROM syscolumns c INNER JOIN sysobjects o ON c.id=o.id
WHERE o.xtype='U' AND c.name='comment'
After executing the above query, copy the results into another query
window and execute them.
We can stop here, but let's make it more automatic: we can use a WHILE
loop to execute each statement (yes, this resembles a cursor very
much...) that puts the results in a temporary table:
CREATE TABLE #results (table_name sysname, comment ntext, sub_string
varchar(50))
create table #statements (id int identity primary key, sql
nvarchar(4000))
insert into #statements
SELECT '
INSERT INTO #results SELECT '''+o.name+''' as table_name, comment,
sub_string
FROM ['+o.name+'] t INNER JOIN #substrings x
ON t.comment LIKE ''%''+sub_string+''%''
' FROM syscolumns c INNER JOIN sysobjects o ON c.id=o.id
WHERE o.xtype='U' AND c.name='comment'
declare @.id int, @.sql nvarchar(4000)
set @.id=0
while 1=1 begin
set @.id=(select min(id) from #statements where id>@.id)
if @.id is null break
select @.sql=sql from #statements where id=@.id
exec (@.sql)
end
drop table #statements
select * from #results
drop table #results
If you don't like the WHILE loop, we can write something shorter, using
an undocumented stored procedure: sp_execresultset (of course, using
undocumented features is not recommended, but this is a one-time job,
isn't it?)
CREATE TABLE #results (table_name sysname, comment ntext, sub_string
varchar(50))
EXEC sp_execresultset '
SELECT ''
INSERT INTO #results SELECT '''+o.name+''' as table_name,
comment, sub_string
FROM [''+o.name+''] t INNER JOIN #substrings x
ON t.comment LIKE ''''%''''+sub_string+''''%''''
'' FROM syscolumns c INNER JOIN sysobjects o ON c.id=o.id
WHERE o.xtype=''U'' AND c.name=''comment''
'
select * from #results
drop table #results
Razvan|||Thanks for the code Razvan!|||Steve wrote:
> A while back a db expert I was talking to expressed the opinion that
> cursors were overused, mostly by programmers who were thinking like
> programmers instead of db people.
> I have a task to do and I'm not sure I can do it without a cursor.
> I'm curious to see if anyone can think of a way around it.
> In a nutshell I want to query the database to find all tables that have
> a particular field( "comment"), include only results where that field
> has one of 20 substrings in the content, and then print it all out.
> So I have
> (substring1, substring2 substring3 ...substring20)
> and I would like to get output like
> table_name comment substring
> -- -- --
> If I can find another way than using cursors or brute force ( many
> cut-n-pasted tsql statements ) I would be grateful.
> Thanks in advance for any thoughts
> Steve
A strange thing to want to do at runtime. Why don't you know what
columns exist in your database? Sensible data manipulation requirements
against a known, static database schema can indeed be done 99.99% of
the time without using cursors.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--