CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Indika Pathirage
Indika Pathirage

Reputation: 29

store the return value from a procedure in a variable

I am trying to execute a procedure(A) inside another procedure(B) and I want to store the value returned from the procedure A in a variable called @LogId.

after executing Procedure A 

exec TES_usp_getnextlogid

it returns something like 

NewLogId = xxxx

I tried doing the following in procedure B. 

declare @LogId int
exec @LogId = TES_usp_getnextlogid

my question is, why doesn't @LogId holds the value which is returned from TES_usp_getnextlogid Procedure?

Thanks.

Upvotes: 0

Views: 58

Answers (1)

M.Ali
M.Ali

Reputation: 69494

use output parameter for this, return value should only be used for returning Success/Faliuer status.

CREATE PROCEDURE My_Outter_Proc2
AS
BEGIN
   DECLARE @NextID INT;

  EXEC TES_usp_getnextlogid @NextID OUTPUT

  EXEC My_Inner_Proc @NextID 

END

Upvotes: 1

Related Questions