June 20, 2012 at 3:37 am
I have a Stored Procedure which returns results set in 2 tables.
For example, if i execute it using EXEC myproc, it returns the output data as shown below.
Table1:
Name Count
xxx 5
yyy 6
Table2:
Place
fff
ccc
I want to use the output data tables of this Stored proc in other procedure.
How can i do that?
NOTE:
If the SP returns a single table structure, i would have used the temp table to hold the output data. Here in this case it is returning data in multiple tables.
Any help on this is greatly appreciated.
June 20, 2012 at 3:42 am
Someone will probably shoot me for suggesting this but:
1. Instead of SELECTing your results out of the SP, SELECT ... FOR XML into an XML variable.
2. Do the same with both results tables (separate XML local variables).
3. Now you can SELECT out of the SP the two XML local variables (1 row, two columns) and then shred them in the calling process.
Probably not very efficient and certainly not very elegant.
Another way might be to OUTPUT two table variables. You'd need to define the table definitions with a TYPE statement. Not sure if you can OUTPUT two tables but it may be possible. Or maybe OUTPUT only one table variable and SELECT the other out as the results set of the SP.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 20, 2012 at 4:02 am
Thanks for your suggestions.
One thing i would like to add here is, the stored procedure returning the tabular data sets is locked and i don't have access to read/alter it.
June 20, 2012 at 4:07 am
balaji_kethe (6/20/2012)
Thanks for your suggestions.One thing i would like to add here is, the stored procedure returning the tabular data sets is locked and i don't have access to read/alter it.
That pretty much eliminates my suggestions from consideration then.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 20, 2012 at 4:50 am
The only possible solution I see here is to create a CLR wrapper procedure that captures the output of the original stored procedure and then returns the resultsets one by one, using an additional parameter.
If you only need the first resultset, you can use the LOOPBACK linked server trick I used in this post[/url] to capture the first resultset. Any additional resultset will be discarded.
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply