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

Parameters are always declared as local to SPs and batches. So even when the parameter name in the SP is exactly same as the local variable name used in the calling module these two variables are pointing to two different places which cannot see each other.

Thus to pass the value into SP you have to assign the local variable into the SP parameter.

You followed above rules so your code should work. BUT you did not show how your variables are declared and we cannot help...

This code does similar things what you've described as "It does not work for me" but for me it works, So the problem in your code can be in invalid variable name, missing comma etc.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.AddNums
 @num1 int, 
 @num2 int, 
 @result int OUTPUT, 
 @errString varchar(100) OUTPUT
AS
BEGIN
 SET NOCOUNT ON;
 SET @result = @num1 + @num2
 SET @errString = 'Everything is OK'
END
GO

CREATE PROCEDURE dbo.AddNumsLogResults
 @result int, 
 @errString varchar(100)
AS
BEGIN
 SET NOCOUNT ON;
    PRINT 'The result is ' + CAST(@result AS varchar) + ', Error msg: ' + COALESCE(@errString, 'None')
END
GO

DECLARE @num1 int = 1, @num2 int = 2, @result int, @result2 int, @err varchar(100)
EXEC dbo.AddNums @num1=@num1, @num2=@num2, @result = @result OUTPUT, @errString = @err OUTPUT
EXEC dbo.AddNumsLogResults @result=@result, @errString=@err

Select allOpen in new window

-- Result:  The result is 3, Error msg: Everything is OK

Here is the complete snippet of code. I am beginning to wonder if what I am seeing is not really an error at all. When I look at the tables involved everything is there. Perhaps there is something structured wrong with my begin/end transaction?

/****** Object:  StoredProcedure [dbo].[rbs_PAFeeInsert]    Script Date: 9/02/2019 11:54:48 AM ******/
DROP PROCEDURE [dbo].[rbs_PAFeeInsert]
GO

/****** Object:  StoredProcedure [dbo].[rbs_PAFeeInsert]    Script Date: 9/02/2019 11:54:48 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[rbs_PAFeeInsert]
    @I_PAcontid CHAR(15),
    @I_PAFeeID CHAR(15),
    @I_PAFEEAMOUNT NUMERIC(19,5),
    @I_PAPERCENTCOST NUMERIC(19,5),
    @I_PAPERCENTREVENUE NUMERIC(19,5),
    @I_PARETENTIONPERCENT NUMERIC(19,5)

AS

BEGIN

SET NOCOUNT ON    

DECLARE @TranCount int
DECLARE @I_CONTPART VARCHAR(15)
DECLARE    @I_ErrorState INT
DECLARE @I_ErrorString CHAR(256)
DECLARE    @O_ErrorState INT
DECLARE @O_ErrorString CHAR(256)
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int


SET @I_CONTPART='FEES'

/* If an active transaction cannot be committed, roll it back. */
IF XACT_STATE() = -1
      ROLLBACK TRANSACTION
ELSE
/* If active transaction(s) can be committed, commit it(them). */
IF XACT_STATE() = 1
      WHILE @@TRANCOUNT > 0
            COMMIT TRANSACTION

BEGIN TRANSACTION

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
    IF @O_ErrorString IS NOT NULL
        SET @I_ErrorString = @O_ErrorString
    ELSE
        SET @I_ErrorString = ''
       
    -- Insert a log fee entry for this job
    EXEC rbs_SingleJobInsertLog
    @I_JOBNUMBER = @I_PAcontid,    
    @I_CONTPART = @I_CONTPART,
    @I_ErrorState=@I_ErrorState,
    @I_ErrorString=@I_ErrorString

END TRY
BEGIN CATCH
    SELECT    @ErrMsg = ERROR_MESSAGE(),
            @ErrSeverity = ERROR_SEVERITY()
    RAISERROR(@ErrMsg, @ErrSeverity, 1)
    Select @O_ErrorState, @O_ErrorString
END CATCH

-- Complete the transaction
IF XACT_STATE() = 1
      COMMIT TRANSACTION
ELSE
IF XACT_STATE() = -1
      ROLLBACK TRANSACTION

Select @O_ErrorState, @O_ErrorString
--RETURN 0  -- No errors

END

GO

I need to add further background information here as I struggle with this as I believe this message is misleading. What I am trying to do is import contracts into a database. Contracts are made up of projects and projects are made up of budgets and fee schedules and fee schedules are made up of fees.The error message is coming from a stored procedures that calls 5 stored procedures. Each of these stored procedures inserts record(s) of the aforementioned types. Now, as an example, I run each stored procedure separately I receive no error messages and the contract is completely inserted and there is a log record for each type. If I run the stored procedure that is comprised of these 5 stored procedures for the same contract I get the error message at the very end. The contract is inserted as well as the logs.Do I need to construct the main stored procedure a certain way that accomodates the multiple stored procedures? In this case the import of the contract occurs first, then the project and budgets and finally fee schedules and fees as they are dependent on each other.

So you have several calls to the rbs_SingleJobInsertLog SP in your code.
The error means one of them is missing the last parameter.

If you review all rbs_SingleJobInsertLog SP calls and check all the commas between parameters and parameter names then you should find the problematic place.

Did you try to debug or trace the SP code?

I can run each sp separately and completely insert a contract with no issues. It is only when I run the sp that groups these same sp's together do I see this error. Is there a way to get SQL Profiler to show what is happening within the sp? Right now it just shows the call to the aggregate sp but not what is happening within the sp.

This code was copied and pasted into each of the five sp's.
    -- Insert a log fee entry for this job
    EXEC rbs_SingleJobInsertLog
    @I_JOBNUMBER = @I_PAcontid,    
    @I_CONTPART = @I_CONTPART,
    @I_ErrorState=@I_ErrorState,
    @I_ErrorString=@I_ErrorString

The simple way how to identify the buggy place is to open the SP in SSMS (right click on it and select Modify) and then update the code to allow its run in SSMS. This means to execute all SET commands and then delete all commands up to ALTER PROCEDURE (incl. it) and place DECLARE keyword before the SP parameters and assign input values to these SP parameters (if you have some). Now you may execute this script and SSMS will show the line which generates the error.

Just remember when this error happens the code continues unless you have appropriate error handling in your SPs. So this explains why log entries were created. Some errors are escalated to the calling module so they appear in the calling module instead of the buggy place itself.

It really seems you have one extra calling point to rbs_SingleJobInsertLog which does not contain all necessary parameters. SQL Profiler cannot show it.

I wasted my entire Saturday afternoon staring at this sp trying to find this extraneous call and I do not see it. How do you update the code so you can see it execute? I am going to try using SQL profiler.

OK, this really make no sense. Following your instructions it pointed me to a line that does not exist. In frustration, I exited everything and rebooted the server. I ran the exact same sp with the exact same parameters and now there are no errors. The error message made sense if it was running an old version of my sp. In my script to create the sp i drop the so first and then create it. I do not alter the sp. Is it better to use alter as opposed to drop?

I cannot say what was wrong at your side because I still don't have all necessary info and that info is no more available after the reboot.

DROP/CREATE or ALTER seems to be irrelevant in this case but it can be important in some other scenarios. (DROP is not allowed sometimes.)  

Looking at your code you are not using schema (dbo.) at many places. This is bad practice which may cause problems - the code may behave differently for different users. BUT I cannot say it was the root cause for this issue.

Are you saying you want to see
    EXEC dbo.rbs_SingleJobInsertLog
as opposed to
    EXEC rbs_SingleJobInsertLog?

I can see where that would make sense.

Yes. When you omit the schema then SQL Server generates query plan for each user separately which consumes memory. 

Good to know. Now in my case all these stored procedures are being call from with a C# application. In the application the preceding data connection is set to use the user credentials coming from the application inside a defined database so all SQL security flows down from that connection. So if a user runs the same stored procedure the query execution is generated only the first time. As that user runs other stored procedures more query executions plans are generated. This repeats as each new user starts running stored procedures. Is this how this works?

Roughly yes. The SP compiling and the Query plan creation is done the first time and then after statistics updates which allows to create execution plan which better fits to the existing statistics. Statistics are saying how the rows are distributed in indexes and it affects indexes used for query optimization.

Different query plans for each user are generated if you omit the schema in SP name or inside the SP code. This is because each user may have different default schema with different access rights assigned. If your C# application uses just one set of credentials to connect SQL Server and user rights are solved internally in C# code then just one query plan exists for all users.