> I am tring to create a SProc that takess 2 Input Parameters and then based
> on the result will execute another SProc which will use the first SProcs
> result as an input parameter to the second. This second SProc will return 3
> output parameters to my web application.
> I have the second SProc working(code below) but am having trouble creating
> the first one that will access the second one.
> Second SPRoc
> Some of my questions:
> 1.) In the first SProc Do I have to have the output parameters declared that
> are being returned from the second SProc?
> 2.) How can I execute the second SProc suppling the InputParameter to it.
> Help would be greatly appreciated,
> Ron VEcchi
Pardon me, but could you maybe ask the question again but call
them ProcA and ProcB ? I got confused.
Pending that, I think that you're showing us ProcB which returns
values to your Web application, but you want to call ProcA from
within ProcB, and that is where you are struggling. But I might
not have understood your problem correctly.
It seems to be necessary to declare a variable OUTPUT both in the
beginning stored procedure that receives it and also in the EXEC
statement that calls it. (Abbreviating to OUT seems to be permitted
but not documented in Microsoft SQL Server 2000 Books Online,
whereas cutting EXECUTE back to EXEC is documented, but maybe
this is standard?)
Here's a demonstration of Transact-SQL code calling a stored
procedure that uses one output parameter, and handling the output
value of the parameter. You could place a similar EXEC statement
in ProcB to allow ProcB to receive the values generated by ProcA.
CREATE PROCEDURE #rjac021018c
When I run all of that in Query Analyzer, this is what is shown:
The procedure has given back the altered value of its parameter.