Post reply

Store output in Tables of Store Procedure which gives OUTPUT in more than 1 DataSet (Table)

  • June 22, 2012 at 8:21 am

    #262762

    Dear All,

    Hi I have a Store Procedure which gives OUTPUT in 2 Data Sets (Tables). Now I need to store both data sets output in a table or in 2 different tables.

    How can I achieve this in MS-SQL Server ? Please guide.

    Example: -

    EXEC Pr_Class_School_Dtl @Class = 1

    Then Output is coming in 2 Data Sets (Tables): -

    Data Set (Table) 1: -

    LabelQ1Q2Q3Q4Q5

    Class IABCDE

    Data Set (Table) 2: -

    SchoolsBoard

    OutlookCBSE

    MSRGPV

    MacTest

    If we have only one Data Set(Table) then we can do this as below. But, how can we output of Store Procedures which returns multiple data sets (tables).

    Insert Into #tt (Label, Q1, Q2, Q3, Q4, Q5) EXEC Pr_Class_Question_Dtl @Class = 1

    But, how can we do this for Multiple Data Sets(Tables) in MS-SQL Server ? Please guide.

    Thanks & Regards,

  • June 22, 2012 at 8:41 am

    #1504524

    As far as I know, TSQL cannot consume Multiple Active Result Sets ;

    I've seen multiple posts on the question, but never saw a solution so far.

    most posts suggest breaking out the logic into two seperate procedures(for example) , and then modifying the original stored procedure to return the results of the two or more new, now "child" procedures to support the previous process, but allow other processes to use the child procs easily.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 22, 2012 at 9:13 am

    #1504560

    As a work-around and I hope this helps but the key here is you must dump the result sets into a temp tables. In the stored procedure I usually use the following syntax:

    CREATE PROCEDURE usp_someprocedure

    AS

    BEGIN

    IF OBJECT_ID('tempdb..#sometable') IS NOT NULL

    CREATE TABLE #sometable(...)

    TRUNCATE TABLE #sometable

    /*do work here*/

    INSERT #sometable

    SELECT col1,col2,..., FROM worktables

    END

    Create the temp table(s) before you call the stored procedure. This way the temp tables exists in a higher(?) session than that of the stored procedure.

    IF OBJECT_ID('tempdb..#sometable') IS NOT NULL

    DROP TABLE #sometable

    CREATE TABLE #sometable(...)

    EXEC usp_someprocedure

    SELECT col1,col2,..., FROM #sometable

Viewing 3 posts - 1 through 2 (of 2 total)

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