December 2, 2015 at 12:07 am
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 🙂
December 2, 2015 at 12:43 am
Could you store them in a table variable?
December 2, 2015 at 3:59 am
yes you can, but how will you do that ?
December 2, 2015 at 4:01 am
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
December 2, 2015 at 4:02 am
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
December 2, 2015 at 4:03 am
Aaaah, of course! Thank you Gail
December 2, 2015 at 4:25 am
But i cant modify proc1 .. i can do any changes in proc2 but not the proc1 which has 3 result set.
December 2, 2015 at 4:28 am
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
December 2, 2015 at 4:35 am
You mean There is no other no possibility to capture more than one result set in proc2 with out modifying Proc1 . ?
December 4, 2015 at 10:13 pm
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
December 6, 2015 at 10:40 pm
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
December 7, 2015 at 12:08 am
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
December 7, 2015 at 12:35 am
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.
December 7, 2015 at 1:20 am
Hi,
Thank you very much for the response and solution as well .
Will try to implement .
Regards,
Ravindra
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply