Avatar of rwheeler23
rwheeler23Flag for United States of America

asked on 

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

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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Pavel Celba
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

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

Open in new window

-- Result:  The result is 3, Error msg: Everything is OK
Avatar of rwheeler23
rwheeler23
Flag of United States of America image

ASKER

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


Avatar of rwheeler23
rwheeler23
Flag of United States of America image

ASKER

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.
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

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?
Avatar of rwheeler23
rwheeler23
Flag of United States of America image

ASKER

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
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

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.
Avatar of rwheeler23
rwheeler23
Flag of United States of America image

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of rwheeler23
rwheeler23
Flag of United States of America image

ASKER

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?
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

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.

The "line which does not exist" could be a line from some called module which was throwed up by some CATCH block. Or, as you were saying, this was caused by unexplained old SP version call. To solve problems by reboot is not obvious in SQL Server environment because the SQL Server stability and reliability is several levels above the Windows standards... So I don't have any explanation in this case.
Avatar of rwheeler23
rwheeler23
Flag of United States of America image

ASKER

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.
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Yes. When you omit the schema then SQL Server generates query plan for each user separately which consumes memory. 
Avatar of rwheeler23
rwheeler23
Flag of United States of America image

ASKER

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?
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

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.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo