Post reply

URGENT: Combining Stored Procedure Output in single result set

  • November 6, 2009 at 9:15 pm

    #213552

    Hi,

    I want to combine the output of multiple stored procedures in a single result set.

    The output columns of all the stored procedures will be same.

    Ex:

    EXEC sp_getClientsList

    UNION ALL

    EXEC sp_getClientsList

  • November 6, 2009 at 9:59 pm

    #1076233

    I guess there are other ways to do that as well, but create a temp table and then do this,

    INSERT INTO #temptable

    EXEC urstoredprocedure

    INSERT INTO #temptable

    EXEC urstoredprocedure

    Select * from #temptable

    ---------------------------------------------------------------------------------

  • November 6, 2009 at 9:59 pm

    #1076234

    One option would be to use INSERT..EXEC to build up the results in a temporary table or table variable.

    Another would be to use a table valued function instead of procedures.

    Another would be to change the procedures to put their results into a #temp table.

    I could go on, as there are a large number of approaches that would work. Without knowing more about why you are looking to do this, I can't recommend one over another.

    You don't really omit schema prefixes and name your procedures sp_ though, right? 🙂

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 6, 2009 at 10:03 pm

    #1076235

    Nabha (11/6/2009)

    I guess there are other ways to do that as well, but create a temp table and then do this...

    I suppose I should mention that this won't work if the stored procedure (or anything it calls) already does an INSERT...EXEC since they can't be nested. It can also cause surprising side effects (like beginning a distributed transaction) if the procedure accesses data over a linked server.

    I try to avoid that construction in general.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 6, 2009 at 10:33 pm

    #1076241

    Thanks for the Replies.

    We need to access same SP on different databases (which are on different servers ) using it on the Linked Servers.

    we can do it like this:

    INSERT INTO #Temp

    EXEC Reddevil.DBNAME.dbo.sp_GetClientLists

    INSERT INTO #Temp

    EXEC Reddevil.DBNAME.dbo.sp_GetClientLists

    SELECT * FROM #Temp

    But to insert the data into the Temp table from a Linked Server, we need to Allow Remote Client(MSDTC) setting. But for security reasons we need to avoid this..

    Any other solution?

  • November 6, 2009 at 10:35 pm

    #1076242

    Hi Paul,

    I have just given this name 'sp_GetClientLists' for example.

    I cant post my the actual names of databases,tables and SP names.

    Thanks

    Deepak

  • November 6, 2009 at 10:42 pm

    #1076243

    If we are not going to combine the output, multiple resultsets will be generated.

    .Net Front End can combine the Multiple record sets and show the output in the one output.

    But we are calling this SP in Reporting Services, which cant handle the multiple record sets.

    Thanks

    Deepak

  • November 6, 2009 at 10:49 pm

    #1076244

    Knock a quick SSIS package together to read from the linked servers and populate a central table. Access the central table from Reporting Services. Or define a distributed view and read from that.

    You know it would have helped a lot had you mentioned linked servers and reporting services in your initial post. Just letting you know.

    Your application must be pretty Top Secret if the names of objects can't be revealed! 😀

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 6, 2009 at 11:11 pm

    #1076248

    Thanks for the Reply Paul.

    We had a thought about SSIS packages.But that would hamper my performance.

  • November 6, 2009 at 11:35 pm

    #1076253

    deepakkn (11/6/2009)

    Thanks for the Reply Paul.

    We had a thought about SSIS packages.But that would hamper my performance.

    Paul... like the other post, not being snarky here... I just don't know for sure because I don't use it. Will SSIS slow things down compared to doing it in T-SQL?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 6, 2009 at 11:55 pm

    #1076256

    Hi Jeff,

    To generate a SSRS Report, i cant call a SSIS package first and then inserting the data into a table,then fetch the data from that table.

    And my activity is totally dynamic.If i use the SSIS Package,datasource should be created dynamically in the SSIS and needs to be deployed dynamically.Then ,we need to fetch the data.

    Instead of that, we can create the function or views instead of creating the SP.

    I can create a Wrapper SP, with Dynamic Query which consists of all the select statements of the functions from all the server and execute the same in the end.

    SELECT * FROM Reddevil.DBNAME.dbo.fn_GetClientsList

    UNION ALL

    SELECT * FROM Reddevil.DBNAME.dbo.fn_GetClientsList

    I am just expecting any other way (Other than temp table) to combine the output of the two SPs which have the same output.

    Thanks

    Deepak

  • November 7, 2009 at 12:13 am

    #1076259

    Hi jeff,

    We can do with distributed Partitioned Views, if we dont find any other solution(for previous one).

    But I guess in this scenario, SSIS will become over head.

    Thanks

    Deepak

  • November 7, 2009 at 12:25 am

    #1076260

    Jeff Moden (11/6/2009)

    deepakkn (11/6/2009)

    Thanks for the Reply Paul.

    We had a thought about SSIS packages.But that would hamper my performance.

    Paul... like the other post, not being snarky here... I just don't know for sure because I don't use it. Will SSIS slow things down compared to doing it in T-SQL?

    To be honest Jeff, I was losing interest slightly because the requirements were changing with every post. At my current workplace, we use regularly scheduled SSIS packages to move data collected on distributed servers to a central point, primarily to make reporting easier.

    Sure, this introduces a latency (though the SSIS packages are designed to copy just new or modified rows, so they are relatively fast). Whether a one-minute old snapshot is suitable in this case is something we will only know three or four posts from now, when we know the full details - and yes that is me at Snark Factor 1 😀

    If the reports absolutely require to be up-to-the-second, then one of the other solutions would be better suited. You certainly wouldn't call the SSIS package synchronously - well almost never 😉

    BTW Jeff, you never have to qualify your remarks - I 'know' you well enough by now!

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 7, 2009 at 7:43 am

    #1076332

    Thanks for the info, Paul.

    Switching gears... I'll always be humble when it comes to something I don't know, though. 🙂 It's just my nature.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 7, 2009 at 7:51 am

    #1076336

    deepakkn (11/6/2009)

    Hi,

    I want to combine the output of multiple stored procedures in a single result set.

    The output columns of all the stored procedures will be same.

    Ex:

    EXEC sp_getClientsList

    UNION ALL

    EXEC sp_getClientsList

    I agree with Paul... the requirements seem to be changing over time a bit. While I have an appreciation for "requirements" changing as knowledge is gained, let's see if we can nail this down a bit. I realize that some of these questions have been answered but it would be nice to see everything in one spot...

    1. What is the over all purpose that we're actually trying to accomplish? IE, describe the process, the source of data, and the ultimate use of the data. A numbered list of process steps would be really nice. As you write the list, keep in mind that there may be a better way than aggregating the output from a bunch of stored procedures... don't presuppose that you should execute those procedures to get the job done. The requirements haven't changed... only your methods have. Suppress the urge in include methods in the descriptions. Describe goals, not methods.

    2. What do all these stored procedures that you think you want to use actually do? Are the all identical? If not, why are they different?

    3. Can you use dynamic SQL on your system?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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