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
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.
Open in new window
-- Result: The result is 3, Error msg: Everything is OK