July 12, 2007 at 10:42 am
Greetings, I need help with trying to call a stored procedure from w/in another. I seem to be calling it OK, but I end up with two result sets. How can I call an sproc from w/in an sproc but not have the results in the output?
For example: Let's say I have sproc A and B. I wish to call B from w/in A. Sproc B returns a value that I wish to use in A. When I call B from with A using following syntax:
DECLARE @Foo INT
EXECUTE @Foo= sprocB
SELECT .. FROM ... WHERE @Foo= 1
I end up with two result sets. If I take the code out of sprocB and put directly in the outer sproc (A in this example) I get only 1 result set.
How can I get only 1 result set, in my case the outer sproc A?
Thanks!
July 12, 2007 at 1:06 pm
Using the syntax EXECUTE @Foo= sprocB will set @Foo to the RETURN value of sprocB. If sprocB issues a SELECT statement, then that result set will be output. My guess is that you've got a SELECT statement in sprocB that returns a single value in a result set. What you'd want to do is do something like this in sprocB (assuming my assumption is correct)
DECLARE @myreturnvalue INT
SELECT @myreturnvalue = -- the rest of your original select statement here
RETURN (@myreturnvalue)
In that case, then @Foo from sprocA will be set to the @myreturnvalue.
July 12, 2007 at 1:16 pm
You are correct in your assumptions. I will give it a go.
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply