troubleshooting Question

Using output variables in a call to another stored procedure from within the same stored procedure.

Avatar of rwheeler23
rwheeler23Flag for United States of America asked on
Microsoft SQL ServerSQL
14 Comments1 Solution26 ViewsLast Modified:
I have a SQL stored procedure that calls two other stored procedures. The first procedure writes data to a table and the second writes a record to a log table. The first procedure has two error code values that are returned. I want to write these error codes to a log file. I found that I had to assign these values to other variables SET @I_ErrorState = @O_ErrorState and       SET @I_ErrorString = @O_ErrorString, otherwise I would get this get message : "Procedure or function 'rbs_SingleJobInsertLog' expects parameter '@I_ErrorString', which was not supplied.". Why is this required?
      

BEGIN TRY
      exec taPAProjectFee
      @I_vPAPROJNUMBER=@I_PAcontid,
      @I_vPAFeeID=@I_PAFeeID,
      @I_vPAFEEAMOUNT=@I_PAFEEAMOUNT,
      @I_vPAPERCENT_COST=@I_PAPERCENTCOST,
      @I_vPAPERCENT_REVENUE=@I_PAPERCENTREVENUE,
      @I_vPA_RETENTION_PERCENT=@I_PARETENTIONPERCENT,
      @O_iErrorState = @O_ErrorState OUTPUT,
      @oErrString = @O_ErrorString OUTPUT
                    
      SET @I_ErrorState = @O_ErrorState
      SET @I_ErrorString = @O_ErrorString

    -- Insert a log project entry for this job
      EXEC rbs_SingleJobInsertLog
      @JOBNUMBER = @I_PAcontid,      
      @I_CONTPART = @I_CONTPART,
      @I_ErrorState=@I_ErrorState,
      @I_ErrorString=@I_ErrorString

END TRY

--- And this does not work
BEGIN TRY
      exec taPAProjectFee
      @I_vPAPROJNUMBER=@I_PAcontid,
      @I_vPAFeeID=@I_PAFeeID,
      @I_vPAFEEAMOUNT=@I_PAFEEAMOUNT,
      @I_vPAPERCENT_COST=@I_PAPERCENTCOST,
      @I_vPAPERCENT_REVENUE=@I_PAPERCENTREVENUE,
      @I_vPA_RETENTION_PERCENT=@I_PARETENTIONPERCENT,
      @O_iErrorState = @O_ErrorState OUTPUT,
      @oErrString = @O_ErrorString OUTPUT


    -- Insert a log project entry for this job
      EXEC rbs_SingleJobInsertLog
      @JOBNUMBER = @I_PAcontid,      
      @I_CONTPART = @I_CONTPART,
      @I_ErrorState=@O_ErrorState,
      @I_ErrorString=@O_ErrorString

END TRY
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 14 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros