参数列表中的大小写表达式?

我目前有两次调用存储过程的调用,这些存储过程根据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),而其他参数则没有。

tigergao000 回答:参数列表中的大小写表达式?

在不需要该参数时,我只是传递NULL或另一个默认值:

IF (@SubmissionStatus = 'REJ' or @SubmissionStatus = 'ACP') and (@LoadUsage = '|CreateBatch|')
BEGIN 
    SET @ExternalBatchNumber = null;
    SET @FrequencyType = null;
END;

然后,您可以简单地使用第二个版本的ii_UpdateClaimSubmission呼叫。

本文链接:https://www.f2er.com/3164497.html

大家都在问