June 5, 2007 at 5:54 pm
Hi,
I have two stored procedures that returns a SELECT result.
I want to be able to return this results, from a third stored procedure, according to a parameter.
Something like this:
IF (@DisplayUnknown = 0)
BEGIN
EXEC stpSnapshotNormal(@Type, @dtDate)
END
ELSE
BEGIN
EXEC stpSnapshotUnknown(@Type, @dtDate)
END
This of course does not work.
Does anyone knows how is it possible to make work?
I know there are others way of doing this, like a function or just to write it all in one stp (oh noes!), but I'm really interested in finding the way it's done from within one stp.
Thanks!
June 5, 2007 at 6:23 pm
June 5, 2007 at 7:10 pm
Is this what are you trying to do?:
CREATE myProc
@DisplayUnknown BIT,
@Type CHAR(1),
@dtDate DATETIME
AS
IF (@DisplayUnknown = 0)
BEGIN
EXEC stpSnapshotNormal @Type, @dtDate
END
ELSE
BEGIN
EXEC stpSnapshotUnknown @Type, @dtDate
END
Based on my coding standards I would call the SP like this
EXEC stpSnapshotUnknown @Type = @Type, @dtDate = @dtDate
But this is up to you. If you don't mess up the order of the parameters it would work the way you have it written. Just drop the ()
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
June 6, 2007 at 3:31 am
Hi AndyDev
Though your code is looking nice by splitting tasks in two sps, It could become a bottleneck if you use Insert..Exec to get results of both sps in temp tables and then use Insert..Exec again in your third stored procedure, since Insert..Exec can't be nested. One another way you might face same issue if you are building test cases for your stored procedure. Normally Test cases use Insert..Exec to get actual results of procedure and then compares it with expected results.
So If you are sure that, your third stored procedure won't have Insert..Exec scenario use it else I would suggest you to think of stored procedures that returns a query string. Execute this string with sp_executesql, you can always put this code inside stored procedure.
Regards
Shrikant Kulkarni
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply