May 16, 2011 at 11:35 am
I tried searching the site without any luck. I figure there must already be several articles/posts on this topic if someone wouldn't mind pointing me in the right direction?
My question:
I want to call a stored procedure that calls another stored procedure and return the result set of the second stored procedure to the original caller.
Example:
Report shows most recent customer sales per sales agent
We have a sproc called GetSalesByAgentAndYear
The first sproc would call GetSalesByAgentAndYear and pass in the Agent and Year. For reasons that I can't think of a good example for, the Year is not known to the calling SSRS report. It must be first looked up inside the first sproc and then passed along with AgentID to GetSalesByAgentAndYear.
I want to return the entire result set from the second sproc as the result set of the first sproc back to SSRS.
What are my options?
I could store it in a temp table and return that.
Are there any other options out there?
Basically I am just curious about a discussion of the pros/cons.
(Also my example above is weak, but for purposes of this problem assume we have no way to know the "YearID" until after running the first sproc... otherwise it would make far more sense to just call the second directly I know and bypass the first sproc entirely)
May 16, 2011 at 12:30 pm
Maxer (5/16/2011)
...I could store it in a temp table and return that.
...
AFAIK the scope of a returned result set is the calling proc, unless you store it in a table (persisted or temptb) and re-select that table (set) to return to a higher level calling proc.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 16, 2011 at 12:37 pm
A dataset returned the calling proc is, by default, returned to the client.
Here's a proof-of-concept demo:
USE ProofOfConcept;
GO
CREATE PROC MyInnerProc
(@Param_in INT)
AS
SELECT @Param_in AS MyInnerProcValue;
GO
CREATE PROC MyOuterProc
AS
EXEC MyInnerProc 1;
GO
EXEC MyOuterProc;
Is there a reason that won't do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 16, 2011 at 1:53 pm
GSquared (5/16/2011)
A dataset returned the calling proc is, by default, returned to the client.Here's a proof-of-concept demo:
USE ProofOfConcept;GO
CREATE PROC MyInnerProc
(@Param_in INT)
AS
SELECT @Param_in AS MyInnerProcValue;
GO
CREATE PROC MyOuterProc
AS
EXEC MyInnerProc 1;
GO
EXEC MyOuterProc;
Is there a reason that won't do what you need?
Because I am an idiot.... 🙂
Nevermind!
🙂
I don't know WHY, but for some reason I was thinking I couldn't just do that... no idea why I was thinking that...
May 16, 2011 at 11:53 pm
dito.
Never seen it being used that way, never been pointed to this "feature" at any course followed and so we just never used it that way, but this opens a whole new interesting overhead cut on all those searcher sprocs that have been split because of performance tuning reasons for the selector query.
After all these years .... :blush:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 18, 2011 at 9:11 am
It's sometimes surprising what blindspots we don't realize we have.
I ran into a minor one at the SQL Rally last week.
I thought this would be non-SARGable and would result in a scan:
select MyColumn
from MyTable
where cast(MyDateTimeColumn as Date) = '1/1/2001';
Turns out, there's optimization built into SQL 2008 that deals with conversion from DateTime to Date, which allows index seeks in that circumstance.
I would never have even TRIED that piece of code, out of the habit of avoiding functions in Where clauses. Total blindspot for me.
They happen. Don't beat yourself up over them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply