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

  • 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 🙂

  • Could you store them in a table variable?

  • yes you can, but how will you do that ?

  • 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
  • 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
  • Aaaah, of course! Thank you Gail

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

  • 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
  • You mean There is no other no possibility to capture more than one result set in proc2 with out modifying Proc1 . ?

  • 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

  • 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

  • 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

  • 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.

  • 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