how to use multiple result set from a procedure inside other procedure

Question

Post reply

how to use multiple result set from a procedure inside other procedure

ravi@sql

SSCrazy

Points: 2121
More actions
December 2, 2015 at 12:07 am

#306686

Hi Champs,
How to make use of a procedure which returns multiple result set in other procedure ?
proc1: returns say 3 different result set
proc2: need to catch those 3 result set in proc2 for other purpose .
How to achieve this ?
Regards,
Ravi 🙂

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Justin Manning SA Hall of Fame Points: 3190 More actions · Answer 1

Justin Manning SA

Hall of Fame

Points: 3190

December 2, 2015 at 12:43 am

#1843411

Could you store them in a table variable?

ravi@sql SSCrazy Points: 2121 More actions · Answer 2

ravi@sql

SSCrazy

Points: 2121

December 2, 2015 at 3:59 am

#1843448

yes you can, but how will you do that ?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Justin Manning SA (12/2/2015)
Could you store them in a table variable?

It would need to be a temp table, because table variables have a scope of the current procedure only, they're not visible in procedures called from the one they're declared in

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

ravi@sql (12/2/2015)
yes you can, but how will you do that ?

In procedure 1, create three temp tables. In procedure 2, insert into those temp tables. Back in Procedure 1, use the temp tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Justin Manning SA Hall of Fame Points: 3190 More actions · Answer 5

Justin Manning SA

Hall of Fame

Points: 3190

December 2, 2015 at 4:03 am

#1843451

Aaaah, of course! Thank you Gail

ravi@sql SSCrazy Points: 2121 More actions · Answer 6

But i cant modify proc1 .. i can do any changes in proc2 but not the proc1 which has 3 result set.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

Then you may just have to re-implement the code from Proc 1.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ravi@sql SSCrazy Points: 2121 More actions · Answer 8

You mean There is no other no possibility to capture more than one result set in proc2 with out modifying Proc1 . ?

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 9

You said proc1 delivers three different resultsets. Different is a disqualifier. INSERT...EXEC supports capturing multiple resultsets into one temp table however only when all resultsets have the same shape. Even with that neat behavior available nothing tells us which resultset each row originally belonged to unless you can derive that knowledge from something in the data.

use tempdb

go

create proc dbo.proc1

as

select 1 as one;

select 2 as two;

select 3 as three;

go

create proc dbo.proc2

as

create table #rs (int_column int);

insert into #rs (int_column) exec dbo.proc1;

select * from #rs;

go

exec dbo.proc2;

drop proc dbo.proc1;

drop proc dbo.proc2;

Now change one of the resultsets in dbo.proc1 to return 2 columns instead of 1 and you'll see error:

Msg 213, Level 16, State 7, Procedure proc1, Line 22
Column name or number of supplied values does not match table definition.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

ravi@sql SSCrazy Points: 2121 More actions · Answer 10

HI,

Thanks for reply ... as you said if we have same result set then it works . But i have 3 different result set with different data type as well .

Regards,

Ravi

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 11

You can do this if you switch to implementing Proc2 as a SQLCLR Stored Procedure instead of a T-SQL Stored Procedure. In a SQLCLR proc you can capture all resultsets from Proc1 into an ADO.NET DataSet using an ADO.NET DataAdapter and then do what you need to with the results after that.

Here is the technique explained that you can port for use inside a SQLCLR procedure: Populating a DataSet from a DataAdapter

From the article:

Multiple Result Sets
--------------------------------------------------------------------------------
If the DataAdapter encounters multiple result sets, it creates multiple tables in the DataSet. The tables are given an incremental default name of TableN, starting with "Table" for Table0. If a table name is passed as an argument to the Fill method, the tables are given an incremental default name of TableNameN, starting with "TableName" for TableName0.

Sample code to populate a DataSet with multiple DataTables representing the multiple resultsets:

string queryString = "EXEC dbo.Proc2;";

SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);

DataSet customers = new DataSet();

adapter.Fill(customers);

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

serg-52 SSCrazy Eights Points: 9802 More actions · Answer 12

Looks like Sql CLR is your last resource. Create CLR proc3 which will call proc1, consume result sets and insert them into temp tables. In proc2 create temp tables and call proc3.

ravi@sql SSCrazy Points: 2121 More actions · Answer 13

Hi,

Thank you very much for the response and solution as well .

Will try to implement .

Regards,

Ravindra