locked
Working with procedures that Return multiple result sets RRS feed

  • Question

  • I am working with an app that uses lots of stored procedures, that return up to 20 result sets each.  If they returned one result set, I could insert the results into a table and continue my querying.  something like CREATE TABLE #TestTable ([name] NVARCHAR(256), [database_ID] INT);
    INSERT INTO #TestTable
    EXEC GetDBNames
    -- Select Table
    SELECT *
    FROM #TestTable;

    But If a Proc returns 10 result sets and I want the third, I can not insert that third data set into a table.

      So I have to open up the procedure and find the code that is returning that third data set and use it in a query.  It would be much easier if I could run the proc and put that third data set into a table.  This way if the app changes I am up to date, I will not copy the code incorrectly, etc.  Plus allot less code to write. 

    Is there a way I can choose one of the data sets and insert it into a table? Thanks,

    Am using SQL 2014

    Mike


    • Edited by Computermike Wednesday, July 27, 2016 3:12 PM CLARIFY
    Wednesday, July 27, 2016 2:00 PM

Answers

All replies

  • I have to admit, that I don't understand your problem.

    Why do you need "scripting them out, finding the code for the result sets I need and then using the code to get my data"? Why not simply using them?

     "Is there a way I can choose one of the data sets and insert it into a table?"

    Sure. But still the same question: Why not using the existing procedures?

    Maybe you can rephrase your question and give us more context.

    Wednesday, July 27, 2016 2:12 PM
  • Do you mean something like this

    insert into myTable (....)

    execute spMyProcedureReturningMultipleResults @param1, @param2 ?

    I need to research on that.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 27, 2016 2:32 PM
  • I want to use the procedure, but I can't find a way to insert the third dataset returned by the procedure into a table.  So, I have to script out the procedures as part of a work around.

    Since I can't insert the third data set into a table from the procedure, I have to open up the procedure and find the code that is returning the third data set. I then use that code in my own query.  This can be allot of work and may not be as accurate as using the procedure.

    Hope this clarifies things, I also updated my original post.

    Thanks, Mike

    Wednesday, July 27, 2016 3:26 PM
  • Hello Mike,

    The stored procedure returns a Multiple Active Result Sets (MARS), which can be easily handle by client components like .NET SqlClient, but not in T-SQL itself.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 27, 2016 4:22 PM
  • TSQL is not capable of handling multiple result sets.  If you are using TSQL, you can't do this. 

    If you are using .NET you can reference Table[2] (0 based) in the results.

    In TSQL, you would need to cut the code out.  I would suggest creating 20 stored procs, one for each result set.  Then change the original stored proc, to call each of the 20 stored procs.  That would simulate the same functionality and allow you to call stored proc #3 by itself, assuming it does not depend on the previous ones.

    • Proposed as answer by Naomi N Wednesday, July 27, 2016 9:23 PM
    Wednesday, July 27, 2016 4:55 PM
  • That Sucks!!!!

    :)

    Thaks

    Wednesday, July 27, 2016 4:57 PM
  • There is no easy way, but look at this article on my web site where I discuss a number of ways to share data between stored procedures:
    http://www.sommarskog.se/share_data.html

    Wednesday, July 27, 2016 5:10 PM
  • Hello Mike,

    Could you please mark one of the replies as answer? I filter this forum on unanswered questions. It would be nice if only threads without an answer would pop up. This may sound as nitpicking to you, but I would appreciate it if you help me save time looking for people that need help. We all also have our real life jobs to do, you see.


    Wednesday, July 27, 2016 8:06 PM