我目前有两次调用存储过程的调用,这些存储过程根据if语句使用不同的参数。有没有一种方法可以在不基于if语句和两个不同调用的情况下,根据情况将case表达式放在要包含/不包含的参数前面?
这是我当前的代码:
if (@SubmissionStatus = 'REJ' or @SubmissionStatus = 'ACP') and (@LoadUsage = '|CreateBatch|')
begin
--when current submission is 'OPT' we change it to 'REJ' before creating a new 'CRT' submission
exec @internal = ii_UpdateclaimSubmission
@Usage = @LoadUsage,@UserId = @UserId,@ClaimSubmissionId = @OldClaimSubmissionId,@SubmissionStatus = @OldSubmissionStatus,--@ExternalBatchNumber = @ExternalBatchNumber,--@FrequencyType = @FrequencyType,@ProcessingStatus = @ProcessingStatus,@TechnicalErrorCode = @RejectReasonCode,@TechnicalErrorDescription = @RejectDescription,@TransactionAcknowledgementCode = @TransactionAcknowledgementCode,@ResubmissionNumber = @ResubmissionNumber,@DoNotSendReason = @DoNotSendReason,@Debugflag = @Debugflag,@InputAdjustmentVersion = @Version,@ErrorMsg = @UserMsg out
end
else
begin
-- we are updating an existing submission to potentially close it out,and not creating a new submission
exec @internal = ii_UpdateclaimSubmission
@Usage = @LoadUsage,@ExternalBatchNumber = @ExternalBatchNumber,@FrequencyType = @FrequencyType,@SubmissionStatus = @SubmissionStatus,@ErrorMsg = @UserMsg out
在一个参数列表中,我有其他参数(@ ExternalBatchNumber,@ FrequencyType,@ Version),而其他参数则没有。