URGENT: Combining Stored Procedure Output in single result set

Question

Post reply

URGENT: Combining Stored Procedure Output in single result set

deepakkn

Default port

Points: 1430
More actions
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

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

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

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

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

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

Paul White SSC Guru Points: 150467 More actions · Answer 2

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

Paul White SSC Guru Points: 150467 More actions · Answer 3

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

deepakkn Default port Points: 1430 More actions · Answer 4

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?

deepakkn Default port Points: 1430 More actions · Answer 5

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

deepakkn Default port Points: 1430 More actions · Answer 6

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

Paul White SSC Guru Points: 150467 More actions · Answer 7

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

deepakkn Default port Points: 1430 More actions · Answer 8

Thanks for the Reply Paul.

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

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)

deepakkn Default port Points: 1430 More actions · Answer 10

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

deepakkn Default port Points: 1430 More actions · Answer 11

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

Paul White SSC Guru Points: 150467 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 14

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)