Creating Callable Processes

[mortonsa]

I have one procedure (we will call it proc1) that will accept input variables and insert them into a table. Before the procedure exits, it will make a call to another procedure (we will call it proc2) and pass it one of the input variables it received. I want proc2 to take that input variable, use it to do some processing and then return a value to proc1. Proc 1 will then use the variable it received from proc 2 to do some more processing before exiting. Is there anyway to do this? Can I have two seperate procs passing variables back and forth like this? If anybody can tell me how to do this, advise me not to, or point me in a direction that would help me I would greatly appreciate it! Thanks!

 

[tlbroadbent]

You can use output variables in stored procedures. Here is a sample. See SQL BOL for more detaiuls.

Create Procedure Proc1
@var1 int,
@var2 int
As

Declare @var3 int, @var4 varchar(30)

<do some stuff>
<assign vallue to @var3>

--Execute Proc2 with input and output variable
Exec proc2 @var3, @var4 Output

<do some more stuff with @var4>

Go

--------------------------------------

Create Procedure Proc2
@var3 int,
@var4 varchar(30) output
As

<do some stuff>

<Assign value to @var4 to return to Proc1>

Go Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[mortonsa]

Hi Trey,

Thanks for getting back to me so quickly. I have been playing around with what you suggested all morning and I can't seem to get Proc1 to accept the value from Proc2 and use it to update another table correctly. I included a little bit of sample code that may help explain this a little better.

Okay... Here is Proc 2

CREATE PROCEDURE PROC2
@TXDETAILIDY numeric(28),
@output_var numeric(28) output
AS
/******************************************************************************
* Name: PROC2
* Created: 11/20/2001, S. Morton
* Desc: Looks up outcome of latest peer review record for update of TX
******************************************************************************/
DECLARE @_TxPeerReviewIDY numeric(28)
DECLARE @_TxPrvwOutcome numeric(28)
DECLARE @txpeerreviewidy numeric(28)
DECLARE @txprvwoutcome numeric(28)

DECLARE c_PRVW CURSOR
FOR
Select TxPeerReviewIDY, TxPrvwOutcome
FROM TTxPeerReview
WHERE TxDetailIDY = @TxDetailIDY
ORDER BY TxPeerReviewIDY Desc

BEGIN TRANSACTION

OPEN c_PRVW
FETCH NEXT FROM c_PRVW INTO @_TxPeerReviewIDY, @_TxPrvwOutcome
WHILE (@@FETCH_STATUS = 0)
BEGIN
select @txpeerreviewidy = @_txpeerreviewidy
select @txprvwoutcome = @_txprvwoutcome
FETCH NEXT FROM c_PRVW INTO @_TxPeerReviewIDY, @_TxPrvwOutcome
END
CLOSE c_PRVW
DEALLOCATE c_PRVW

If @txprvwoutcome is null
select @output_var = @txpeerreviewidy

If @txprvwoutcome is not null
select @output_var = 0

COMMIT TRANSACTION

--------------------------------------------------------------------------------------------------------------
Okay so Proc2 is either going to return @txpeerreviewidy or 0. On to PROC1....

CREATE PROCEDURE PROC1
@TXPEERREVIEWIDY numeric(28) output,
@v_TXDETAILIDY numeric(28),
@TXPRVWREFERRALDATE datetime = null
AS
/******************************************************************************
* Name: INS_TTXPEERREVIEW
* Created: 10/22/2001, S. Morton
* Modified:
* Desc: Inserts treatment peer review records
******************************************************************************/
DECLARE @RETURN int
DECLARE @MyIdent int

BEGIN TRANSACTION

Insert into TTXPEERREVIEW (
TXDETAILIDY,
TXPRVWREFERRALDATE)
values (
@v_TXDETAILIDY,
@TXPRVWREFERRALDATE)

Exec @Return = PROC2
@TXDETAILIDY = @v_TXDETAILIDY,
@OUTPUT_VAR = @MyIdent output

If @Return <> 0
Update TTXDETAIL
SET TxLockInd = 'Y'
Where TxDetailIDY = @v_TXDETAILIDY
If @Return = 0
Update TTXDETAIL
SET TxLockInd = 'N'
Where TxDetailIDY = @v_TXDETAILIDY


My insert procedure is working fine but PROC1 is having trouble getting the return value from proc2 and using it to update TTXDETAIL table with a value of Y or N. I'm not sure if this syntax is right since I have been pulling from a few different books. Any help you can give me is appreciated.

Thanks,
Stephany

 

[tlbroadbent]

Stored procedures return output variables to the variable you declared not via a return statement. You value will return from Proc2 into the variable @MyIdent rather than @Return.

In this case you don't need the @return variable.

Exec = PROC2
@TXDETAILIDY = @v_TXDETAILIDY,
@OUTPUT_VAR = @MyIdent output

If @MyIdent <> 0
Update TTXDETAIL
SET TxLockInd = 'Y'
Where TxDetailIDY = @v_TXDETAILIDY
Else
Update TTXDETAIL
SET TxLockInd = 'N'
Where TxDetailIDY = @v_TXDETAILIDY Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.