Multiple ResultSets

We have a third party stored proc which returns multiple resultsets. Their
format is well defined.
Is it possible to capture the multiple resultsets in temp table. Something
like

insert into #temp
exec sp_GetTables


thx

I don't believe this is possible in TSQL (could be mistaken), but you could
use a different language to capture and process multiple resultsets from a
sproc.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"slg" wrote in message
news:ey8AT2JCJHA.4368-at-TK2MSFTNGP06.phx.gbl...
> We have a third party stored proc which returns multiple resultsets. Their
> format is well defined.
> Is it possible to capture the multiple resultsets in temp table.
> Something like
>
> insert into #temp
> exec sp_GetTables
>
>
> thx

slg
Do the multiple resultsets have the same number of columns to return? If it
does not you are out of luck.
If it does, create a temporary table and insert into eacgh result set but
it is messy




"slg" wrote in message
news:ey8AT2JCJHA.4368-at-TK2MSFTNGP06.phx.gbl...
> We have a third party stored proc which returns multiple resultsets. Their
> format is well defined.
> Is it possible to capture the multiple resultsets in temp table.
> Something like
>
> insert into #temp
> exec sp_GetTables
>
>
> thx

I think you can only capture the first resultset.

Even if the columns are identical from the resultsets, I still think you can
only capture the 1st resultset.

............




"slg" wrote in message
news:ey8AT2JCJHA.4368-at-TK2MSFTNGP06.phx.gbl...
> We have a third party stored proc which returns multiple resultsets. Their
> format is well defined.
> Is it possible to capture the multiple resultsets in temp table.
> Something like
>
> insert into #temp
> exec sp_GetTables
>
>
> thx

Wrong!

-- If the resultsets are all the same structure and there is something about
them which distinguishes them, you can
-- capture them in a table and filter them afterwards eg

DROP TABLE temp
GO
CREATE TABLE temp ( source_id INT, val1 VARCHAR(10), val2 VARCHAR(10),
date_added DATETIME DEFAULT( GETDATE() ) )
GO
CREATE VIEW viw_temp AS SELECT source_id, val1, val2 FROM temp
GO

DROP PROC usp_test
GO
CREATE PROC usp_test

AS

SELECT 1 AS source, 10, 100

SELECT 2 AS source, 20, 200
GO

-- Capture all results of the stored proc
INSERT INTO viw_temp
EXEC usp_test
GO

-- Show everything
SELECT *
FROM temp
GO

-- Filter resultset
SELECT *
FROM temp
WHERE source_id = 1
GO

Or see here with a CLR method:
http://groups.google.com/group/micro...55f2e?lnk=raot

"sloan" wrote:

>
> I think you can only capture the first resultset.
>
> Even if the columns are identical from the resultsets, I still think you can
> only capture the 1st resultset.
>
> ............
>
>
>
>
> "slg" wrote in message
> news:ey8AT2JCJHA.4368-at-TK2MSFTNGP06.phx.gbl...
> > We have a third party stored proc which returns multiple resultsets. Their
> > format is well defined.
> > Is it possible to capture the multiple resultsets in temp table.
> > Something like
> >
> > insert into #temp
> > exec sp_GetTables
> >
> >
> > thx
>
>
>

Ouch. My bad (to the original OP).

Thanks for the correction.


"Bob" wrote in message
news:4EFC9722-5563-4C74-955E-0A74E9D85CBD-at-microsoft.com...
> Wrong!
>
> -- If the resultsets are all the same structure and there is something
> about
> them which distinguishes them, you can
> -- capture them in a table and filter them afterwards eg
>
> DROP TABLE temp
> GO
> CREATE TABLE temp ( source_id INT, val1 VARCHAR(10), val2 VARCHAR(10),
> date_added DATETIME DEFAULT( GETDATE() ) )
> GO
> CREATE VIEW viw_temp AS SELECT source_id, val1, val2 FROM temp
> GO
>
> DROP PROC usp_test
> GO
> CREATE PROC usp_test
>
> AS
>
> SELECT 1 AS source, 10, 100
>
> SELECT 2 AS source, 20, 200
> GO
>
> -- Capture all results of the stored proc
> INSERT INTO viw_temp
> EXEC usp_test
> GO
>
> -- Show everything
> SELECT *
> FROM temp
> GO
>
> -- Filter resultset
> SELECT *
> FROM temp
> WHERE source_id = 1
> GO
>
> Or see here with a CLR method:
> http://groups.google.com/group/micro...55f2e?lnk=raot
>
> "sloan" wrote:
>
>>
>> I think you can only capture the first resultset.
>>
>> Even if the columns are identical from the resultsets, I still think you
>> can
>> only capture the 1st resultset.
>>
>> ............
>>
>>
>>
>>
>> "slg" wrote in message
>> news:ey8AT2JCJHA.4368-at-TK2MSFTNGP06.phx.gbl...
>> > We have a third party stored proc which returns multiple resultsets.
>> > Their
>> > format is well defined.
>> > Is it possible to capture the multiple resultsets in temp table.
>> > Something like
>> >
>> > insert into #temp
>> > exec sp_GetTables
>> >
>> >
>> > thx
>>
>>
>>