Access multiple recordsets from SP using SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Access multiple recordsets from SP using SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-27 : 11:16:48

Dennis writes "First, Thanks in advance

Let's say I have a stored procedure that comprises of two select staments,
for example:

create proc MyProc as
select * from A
select * from B
end proc

when I execute this stored procedure I get two record sets (of course)

I want to call this stored procedure from another stored procedure then go
through the recordsets individually:
That is, if I call MyProc like this

create proc TopProc as
exec MyProc
---- I want to manipulate the two recordsets returned from the above "exec" statement SEPARATELY using SQL ----
end proc

Thanks!"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-27 : 11:22:05

You're mixing semantics. Stored procedures don't return recordsets; they are an ADO abstraction. Stored procedures return rowsets. Are the rowset definitions the same ( column types and #'s match )? If so you can UNION them and then access the rowset as follows:

create proc a
select * from table_a
union
select * from table_b

create proc b

create table #temp ( ... )
insert #temp
exec a

setBasedIsTheTruepath
<O>

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-03-27 : 11:26:22

What about putting the "recordsets" into a temp table, manipulate them with t-sql, and then do a select on the temp table to return the data you need?

SELECT * INTO #TempA FROM (SELECT * FROM a)
SELECT * INTO #TempB FROM (SELECT * FROM b)

--Insert code here to manipulate #TempA and #TempB

SELECT * from #TempA
SELECT * from #TempB

DROP TABLE #TempA
DROP TABLE #TempA

If the data is exactly the same, it's possible you could put all the data into one temp table. If there are 100'sk-1000k records or so, temp tables might not be such a good idea. Just try it out and see how performance is with your particular datasets.

Michael

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-03-27 : 11:28:26

Sniped! But we had basically the same idea :)

<Yoda>Set based, true path it is....yesssss</Yoda>

Michael

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-27 : 11:35:54

So nice to be loved