Multi Record sets from SProcs

microsoft.public.sqlserver.programming

13 Sep 2006 7:43 PM

rmg66

SQL SERVER 2000

I have a stored procedure that returns multiple recordsets (all the same colums, datatypes etc...).
I need to evaluate the data in each record set.

I attempted to insert the results of the stored procedure into a temp table.
eg:
create table #table (col1 varchar(1000) null, col2 varchar(10000)

insert into #table
exec stored_proc

select * from #table

This, of course, works when the sproc returns a single recordset, but with multiples, it fails.

Is there a way to insert this data?
or
Is there any other way to evaluate the results programatically?

MORE SPECIFICALLY:

I need to find out if a certian registry value exists
exact code:
create table #reg (value varchar(1000) null, data varchar(1000) null)

insert into #reg
exec master.dbo.xp_regenumvalues
'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'

select * from #reg where value = 'DefaultData'

Is there any other way to get this information using TSQL?

13 Sep 2006 7:58 PM

Ken

rmg66 wrote:
Show quote

> SQL SERVER 2000
>
> I have a stored procedure that returns multiple recordsets (all the same colums, datatypes etc...).
> I need to evaluate the data in each record set.
>
> I attempted to insert the results of the stored procedure into a temp table.
> eg:
> create table #table (col1 varchar(1000) null, col2 varchar(10000)
>
> insert into #table
> exec stored_proc
>
> select * from #table
>
> This, of course, works when the sproc returns a single recordset, but with multiples, it fails.
>
> Is there a way to insert this data?
> or
> Is there any other way to evaluate the results programatically?
>
>
> MORE SPECIFICALLY:
>
> I need to find out if a certian registry value exists
> exact code:
> create table #reg (value varchar(1000) null, data varchar(1000) null)
>
> insert into #reg
> exec master.dbo.xp_regenumvalues
> 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'
>
> select * from #reg where value = 'DefaultData'
>
> Is there any other way to get this information using TSQL?
> ------=_NextPart_000_0006_01C6D74B.4F12A9A0
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 1958
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.2900.2802" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=#ffffff background="">
> <DIV><FONT face=Arial size=2>SQL SERVER 2000<BR><BR>I have a stored procedure
> that returns multiple recordsets (all the same colums, datatypes etc...).<BR>I
> need to evaluate the data in each record set.<BR><BR>I attempted to insert the
> results of the stored procedure into a temp table.<BR>eg:<BR><FONT
> face="Courier New">create table #table (col1 varchar(1000) null, col2
> varchar(10000)</FONT></FONT></DIV><FONT face=Arial size=2><FONT
> face="Courier New">
> <DIV><BR>insert into #table<BR>exec stored_proc</DIV>
> <DIV><FONT face=Arial></FONT><BR>select * from #table</FONT><BR><BR>This, of
> course, works when the sproc returns a single recordset, but with multiples, it
> fails.<BR><BR>Is there a way to insert this data?<BR>or <BR>Is there any other
> way to evaluate the results programatically?<BR></DIV></FONT>
> <DIV><FONT face=Arial size=2><BR>MORE SPECIFICALLY:<BR><BR><EM>I need to find
> out if a certian registry value exists</EM><BR>exact code:</FONT></DIV>
> <DIV><FONT face=Arial size=2><FONT face="Courier New">create table #reg (value
> varchar(1000) null, data varchar(1000) null)</FONT></FONT></DIV><FONT face=Arial
> size=2><FONT face="Courier New">
> <DIV><BR>insert into #reg<BR>exec master.dbo.xp_regenumvalues </DIV>
> <DIV>'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'</DIV>
> <DIV></FONT></FONT> </DIV>
> <DIV><FONT face=Arial size=2><FONT face="Courier New">select * from #reg where
> value = 'DefaultData'</FONT><BR></FONT></DIV>
> <DIV><FONT face=Arial size=2>Is there any other way to get this information
> using TSQL?</DIV></FONT></BODY></HTML>
>
> ------=_NextPart_000_0006_01C6D74B.4F12A9A0--

You can do a union of these recordsets into one recordset using the
UNION in select statement. You cannot capture mulitple recordsets in
TSQL code from a stored procedure. You can only view them in query
analyzer or some other utiltity although ADO will and handle multiple
recordsets.

If you need to identify the separate blocks of data you can add a field
to the recordset and hard code a value in each of the select statements
you are unioning.

create table #table (recid int, col1 varchar(1000) null, col2
varchar(10000)

Select 1, col1, col2 from Source
Union
Select 2, col1, col2 from Source