Exec SProc from SProc (output parameter question)

Exec SProc from SProc (output parameter question)

Post by Ron Vecch » Sat, 19 Oct 2002 19:55:14



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
----------------




As
   Select




GO
-----------------

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

 
 
 

Exec SProc from SProc (output parameter question)

Post by Alejandro Mes » Sat, 19 Oct 2002 21:02:31


This is just an example of what you asked for:


)
AS
SET NOCOUNT ON




FROM orders

SET NOCOUNT OFF
GO


)
AS
SET NOCOUNT ON







SET NOCOUNT OFF
GO














AMB

 
 
 

Exec SProc from SProc (output parameter question)

Post by Robert Carneg » Sun, 20 Oct 2002 00:27:40



> 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
> ----------------




> As
>    Select




> GO
> -----------------

> 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.

USE tempdb
GO

CREATE PROCEDURE #rjac021018c

as
PRINT '#rjac021018c'

GO






When I run all of that in Query Analyzer, this is what is shown:

Robert (before)
#rjac021018c
ROBERT (after)

The procedure has given back the altered value of its parameter.

 
 
 

1. Exec sproc from sproc

I have a proc (Proc_B)that inserts records into a table (temptags) for
use by other procs. Proc_B first deletes all records for the user from
the table (based on a parameter passed in), and then inserts records
based on the query that was passed in.

Proc_A calls Proc_B and then does a join against the table that Proc_B
udpated and returns the result.

My problem is that unless I put a WAITFOR statement in before the call
to Proc_B, there are no records in the table when Proc_A does the join.

an abbreviated form of Proc_A is as follows:
set param1 = 'some dynamic sql'
waitfor delay '000:00:00.001'
exec Proc_B 'param1','Param2','param3'
select f1,f2,... from myview inner join temptags on blah, blah,
blah,etc.

Commenting out the waitfor statement results in 0 records, including
the waitfor statement returns the correct result.

Can someone 'splain to me why Proc_A appears to continue on without
waiting for Proc_B to finish? or what is wrong with this picture. I
have tried including output params from Proc_B, but only the WAITFOR
(or a print statement) seem to work.

Proc_B does the following:
1. deletes records for the specified user from temptags
2. builds some dynamic SQL based on the input params
3. executes the dynamic SQL

Thanks much for any help

Sent via Deja.com http://www.deja.com/
Before you buy.

2. SQL2000 Desktop edition

3. PRB: ADO, CMD, SPROC and output parameters...

4. what??

5. Is there a limit on the No. of input output parameters in ADO for ORACLE SProc

6. osql -i doesn't accept accent in files

7. Sproc as an input parm for another sproc?

8. last insert

9. Calling a sproc from another sproc

10. Calling Sproc from Sproc and getting only one recordset

11. Using sproc resultset in another sproc

12. How to embed CreateTrigger into a sproc or otherwise script it into a sproc

13. Execute a SPROC in another SPROC