Stored Procedure Error

[SA812]

I have a stored procedure that errors out. My question is in the following SQL text.
Where the sp says;
",@EdiCustId"(is that a required field/attribute? Meaning it is looking for that value to be plugged in)

next

Where the sp says;
",NULL --@_iStatus" ( Does that mean a null value/attribute is allowed?)



EXECUTE @RC = [Mas500_app].[dbo].[spsoGetSalesOrdDflts]
@company
,@cust_custKey
,@EdiCustId
,@tranType
,NULL --@_iStatus
,NULL --@_iAckDate
,NULL --@_iBillToCustAddrKey
,NULL --@_iBillToCustAddrID
,NULL --@_iBillToCopyKey
,NULL --@_iBillToCopyID
,NULL --@_iBillToAddrName
,NULL --@_iBTAddrLine1
,NULL --@_iBTAddrLine2
,NULL --@_iBTAddrLine3
,NULL --@_iBTAddrLine4
,NULL --@_iBTAddrLine5
,NULL --@_iBTCity
,NULL --@_iBTState
,NULL --@_iBTCountryID
,NULL --@_iBTPostalCode
,NULL --@_iBTTransactionOverride
,NULL --@_iBlnktRelNo
,NULL --@_iBlnktSOKey
,NULL --@_iBlnktSOID
,NULL --@_iChngOrdDate
,NULL --@_iChngOrdNo
,NULL --@_iChngReason
,NULL --@_iChngUserID
,NULL --@_iCloseDate
,@_iCntctKey
,NULL --@_iCntctName
,@_iCurrExchRate --@_iCurrExchRate
,@_iCurrExchSchdKey --@_iCurrExchSchdKey
,@_iCurrExchSchdID --@_iCurrExchSchdID
,@_oSOKey OUTPUT
,@_oTranID OUTPUT
,@_oRetValSp OUTPUT
-- ,@_iMigration
-- ,@_iRptOption
-- ,@_iPrintWarnings
-- ,@_iBlankInvalidReference
-- ,@_iInvalidGLUseSuspense

/* set for logging */
select @_logSoKey = @_oSOKey
select @_logTranNo = SUBSTRING(@_oTranID, 4, 10)
select @_oTranNo = @_logTranNo

--Select @_oSOKey As SOKey, @_oTranID As TranID, @_oRetValSp As RetVal
--select * from #tsoAPIValid
if @_oRetValSp != 1 and @_oRetValSp != 2
begin
select @oRetVal = @_oRetValSp
/* error log call ****************/
select @_logMessage = 'Failure setting SO defaults with spsoGetSalesOrdDflts'
EXECUTE @RC = [edi].[dbo].[SpSoLog] @_logSoImportKey, @_logSessionKey, @_logSoKey , @_logSoLineImportKey, @_logSoLineKey
,@_logTranNo, @_logCustId, @_logCustPoNo, @_logItemId
,@_logMessage, 1, @oRetVal, @oLogKey = @oLogKey OUTPUT
/*********************************/
select @oErrorKey = @oLogKey
GOTO DROPTABLES
end

Thanks for your help in advance.
SA

 

[markros]

What is the error message?

PluralSight Learning Library

 

[danvlas]

No, it means those values are passed for the corresponding parameters.



Daniel Vlas
Systems Consultant

 

[SA812]

The error is "Failure setting SO defaults with
spsoGetSalesOrdDflts"

Does the NULL mean it will except a null value?
",NULL --@_iCloseDate"

Is there a way to test the stored procedure in a test environment? I'm not a SQL developer.

 

[markros]

You need to invoke this SP in SSMS.

PluralSight Learning Library

 

[danvlas]

You are trying to execute "spsoGetSalesOrdDflts"
That is supposed to be a stored procedure that needs some parameters.

You are passing the VALUES for those parameters in their order.
For example, you are passing the value of the local variable '@company' to the first parameter defined in the procedure. You are passing the value of the local variable ',@cust_custKey' to the second parameter and so on.
Where you pass NULL, you must make sure that the parameter in the procedure is optional (meaning it has a default value defined, even if it is null)
If you pass NULL to a mandatory parameter, you will get the error.

How to see what parameters are optional:

Create Procedure SomeProcName
@MandatoryParam int,
@OptionalParamWithNoValue int = NULL,
@OptionalParamWithValue int=1000

As
--do something

Exec @RC=SomeProcName Null, Null, Null
will error, because the first parameter is mandatory

Exec @RC=SomeProcName 1, Null, Null
will run fine. Second parameter will be Null and the third parameter wil be 1000 within the procedure.

So, what is the definition of spsoGetSalesOrdDflts?


To test a stored proc, you could have a database backup restored and used as a test environment.
Alternatively, you could wrap the tests in a transaction block thatis rolled back in the end, but I would avoid that.

HTH


Daniel Vlas
Systems Consultant