如何在SQL Server视图中集中复用CASE WHEN逻辑？

针对你需要在多个视图中复用相同CASE WHEN逻辑的场景，有三种高效解决方案，可避免重复代码和后续多处修改的麻烦：

方案1：创建内联表值函数（推荐，性能最优） #

内联表值函数会被SQL引擎视为原生查询的一部分，性能接近直接编写CASE WHEN，同时能一次性返回两个动态列，适合多列复用的场景。

创建函数 #

CREATE FUNCTION dbo.GetDynamicColumns
(
    @Code VARCHAR(50),
    @Type1 VARCHAR(50),
    @Type2 VARCHAR(50),
    @Tag1 VARCHAR(50),
    @Tag2 VARCHAR(50),
    @Tag3 VARCHAR(50),
    @Detail VARCHAR(MAX),
    @Code2 VARCHAR(50),
    @Description VARCHAR(MAX),
    @Tag4 VARCHAR(MAX),
    @SubType VARCHAR(50),
    @SubTag VARCHAR(50),
    @SubTag2 VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        -- DynamicColumn1 逻辑
        CASE
            WHEN @Code = 'Condition' THEN ISNULL(@Type1, @Type2)
            WHEN @Tag1 = 'Condition' THEN ISNULL(@Tag2, @Tag3) 
            WHEN @Detail IS NOT NULL THEN 
                CONCAT(
                    CASE 
                        WHEN ISNULL(@Code, @Code2) IS NOT NULL THEN CONCAT(ISNULL(@Code, @Code2), ' ')
                        ELSE ''
                    END,
                    @Detail
                )
            WHEN @Type1 = 'Condition' THEN 
                CONCAT(
                    CASE WHEN @Code = 'Condition' THEN 'Value' ELSE @Code END,
                    ' ', @Description, 
                    ' ', @Type1
                )
            ELSE @Tag1
        END AS DynamicColumn1,
        -- DynamicColumn2 逻辑
        CASE 
            WHEN @Tag1 = 'Condition' THEN REPLACE(@Tag4, 'string', '')
            WHEN @Code = 'Condition' THEN @SubType
            WHEN @Detail IS NOT NULL THEN 
                CASE
                    WHEN ISNULL(@SubTag, @SubTag2) IS NOT NULL THEN
                        CASE 
                            WHEN ISNULL(@SubTag, @SubTag2) = 'N/A' THEN NULL
                            ELSE ISNULL(@SubTag, @SubTag2)
                        END
                END
            ELSE @Tag2
        END AS DynamicColumn2
)

在视图中调用 #

通过CROSS APPLY关联函数，直接获取两个动态列：

CREATE VIEW YourTargetView
AS
SELECT 
    I.*,
    DC.DynamicColumn1,
    DC.DynamicColumn2
FROM YourSourceTable I
CROSS APPLY dbo.GetDynamicColumns(
    I.Code, I.Type1, I.Type2, I.Tag1, I.Tag2, I.Tag3,
    I.Detail, I.Code2, I.Description, I.Tag4, I.SubType,
    I.SubTag, I.SubTag2
) DC

方案2：创建标量值函数 #

如果更倾向于按列拆分逻辑，可以为每个动态列单独创建标量函数，适合只需要单独调用某一列的场景。

创建DynamicColumn1的函数 #

CREATE FUNCTION dbo.GetDynamicColumn1
(
    @Code VARCHAR(50),
    @Type1 VARCHAR(50),
    @Type2 VARCHAR(50),
    @Tag1 VARCHAR(50),
    @Tag2 VARCHAR(50),
    @Tag3 VARCHAR(50),
    @Detail VARCHAR(MAX),
    @Code2 VARCHAR(50),
    @Description VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Result VARCHAR(MAX)
    SET @Result = 
        CASE
            WHEN @Code = 'Condition' THEN ISNULL(@Type1, @Type2)
            WHEN @Tag1 = 'Condition' THEN ISNULL(@Tag2, @Tag3) 
            WHEN @Detail IS NOT NULL THEN 
                CONCAT(
                    CASE 
                        WHEN ISNULL(@Code, @Code2) IS NOT NULL THEN CONCAT(ISNULL(@Code, @Code2), ' ')
                        ELSE ''
                    END,
                    @Detail
                )
            WHEN @Type1 = 'Condition' THEN 
                CONCAT(
                    CASE WHEN @Code = 'Condition' THEN 'Value' ELSE @Code END,
                    ' ', @Description, 
                    ' ', @Type1
                )
            ELSE @Tag1
        END
    RETURN @Result
END

创建DynamicColumn2的函数 #

CREATE FUNCTION dbo.GetDynamicColumn2
(
    @Tag1 VARCHAR(50),
    @Tag4 VARCHAR(MAX),
    @Code VARCHAR(50),
    @SubType VARCHAR(50),
    @Detail VARCHAR(MAX),
    @SubTag VARCHAR(50),
    @SubTag2 VARCHAR(50),
    @Tag2 VARCHAR(50)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Result VARCHAR(MAX)
    SET @Result = 
        CASE 
            WHEN @Tag1 = 'Condition' THEN REPLACE(@Tag4, 'string', '')
            WHEN @Code = 'Condition' THEN @SubType
            WHEN @Detail IS NOT NULL THEN 
                CASE
                    WHEN ISNULL(@SubTag, @SubTag2) IS NOT NULL THEN
                        CASE 
                            WHEN ISNULL(@SubTag, @SubTag2) = 'N/A' THEN NULL
                            ELSE ISNULL(@SubTag, @SubTag2)
                        END
                END
            ELSE @Tag2
        END
    RETURN @Result
END

在视图中调用 #

CREATE VIEW YourTargetView
AS
SELECT 
    I.*,
    dbo.GetDynamicColumn1(I.Code, I.Type1, I.Type2, I.Tag1, I.Tag2, I.Tag3, I.Detail, I.Code2, I.Description) AS DynamicColumn1,
    dbo.GetDynamicColumn2(I.Tag1, I.Tag4, I.Code, I.SubType, I.Detail, I.SubTag, I.SubTag2, I.Tag2) AS DynamicColumn2
FROM YourSourceTable I

方案3：创建公共基础视图 #

如果所有需要复用逻辑的视图都基于同一个源表，可以把动态列逻辑封装到一个基础视图中，其他视图直接基于该视图查询，维护成本最低。

创建基础视图 #

CREATE VIEW BaseViewWithDynamicColumns
AS
SELECT 
    I.*,
    CASE
        WHEN I.Code = 'Condition' THEN ISNULL(Type1, Type2)
        WHEN Tag1 = 'Condition' THEN ISNULL(Tag2, Tag3) 
        WHEN I.Detail IS NOT NULL THEN 
            CONCAT(
                CASE 
                    WHEN ISNULL(Code, Code2) IS NOT NULL THEN CONCAT(ISNULL(Code, Code2), ' ')
                    ELSE ''
                END,
                Detail
            )
        WHEN I.Type1 = 'Condition' THEN 
            CONCAT(
                CASE WHEN I.Code = 'Condition' THEN 'Value' ELSE Code END,
                ' ', I.Description, 
                ' ', I.Type1
            )
        ELSE I.Tag1
    END AS DynamicColumn1,
    CASE 
        WHEN Tag1 = 'Condition' THEN REPLACE(I.Tag4, 'string', '')
        WHEN I.Code = 'Condition' THEN SubType
        WHEN I.Detail IS NOT NULL THEN 
            CASE
                WHEN ISNULL(SubTag, SubTag2) IS NOT NULL THEN
                    CASE 
                        WHEN ISNULL(SubTag, SubTag2) = 'N/A' THEN NULL
                        ELSE ISNULL(SubTag, SubTag2)
                    END
            END
        ELSE Tag2
    END AS DynamicColumn2
FROM YourSourceTable I

在其他视图中复用 #

CREATE VIEW YourOtherView
AS
SELECT 
    DynamicColumn1,
    DynamicColumn2,
    -- 按需选择其他字段
    Id, Name, CreateTime
FROM BaseViewWithDynamicColumns
WHERE -- 你的过滤条件

方案对比 #

内容的提问来源于stack exchange，提问作者David