存储过程中的SQL case语句 [英] SQL case statement in a stored procedure

问题描述

我有一个包含CASE语句的SQL Server存储过程.但是，如果多个条件为真，则需要附加这些值.

I have a SQL Server stored proc that contains a CASE statement. However, I need to append the values if multiple conditions are true.

因此，如果特定记录的日期为无效日期并且里程已超出，我希望两个值都显示在 NotArchiveableReason 列.

So if a particular record has an Invalid Date And Mileage exceeded, I would like both values to be displayed in the NotArchiveableReason column.

我该怎么做?

, CASE 
    WHEN DateOfLoss < PolicyStartDate THEN 'Invalid Date'
    WHEN MilesDriven > TotalMilesAllowed THEN 'Mileage exceeded'
    WHEN LossStatusCode != 'R' THEN 'Status code is Review'
    Else 'Unknown issue'
    END
    As NotArchiveableReason

推荐答案

如果要连接Invalid Date, Mileage Exceeded之类的结果，那么您可能正在寻找类似的东西.

If you want to concatenate results like Invalid Date, Mileage Exceeded then you may be looking for something like this.

ISNULL(
    NULLIF(
        STUFF(
            CASE WHEN DateOfLoss < PolicyStartDate THEN ', Invalid Date' ELSE '' END
            + CASE WHEN MilesDriven > TotalMilesAllowed THEN ', Mileage exceeded' ELSE '' END
            + CASE WHEN LossStatusCode != 'R' THEN ', Status code is Review' ELSE '' END
        , 1, 2, '')
    ,'')
, 'Unknown issue')
As NotArchiveableReason

STUFF()删除前导逗号. NULLIF()将空字符串转换为null. ISNULL()语句条件都不满足时，ISNULL()将填充未知问题".

The STUFF() removes the leading comma. The NULLIF() converts the empty string to null. The ISNULL() will populate "Unknown Issue" when none of the CASE statement conditions are met.

这篇关于存储过程中的SQL case语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！