如何在存储过程中通用处理大量用户定义表类型以减少代码重复？

1. 统一UDTT结构，使用通用表值参数 #

把所有用于筛选的用户定义表类型（UDTT）统一为相同结构，比如包含筛选列名和对应值的通用类型：

CREATE TYPE dbo.GenericFilterType AS TABLE (
    FilterColumn NVARCHAR(128) NOT NULL,
    FilterValue SQL_VARIANT NOT NULL
);

所有存储过程都接收这个通用类型作为参数，无需每次新增UDTT都修改存储过程参数列表。后续处理时直接遍历该表生成条件，不用为每个参数单独写计数、变量赋值逻辑。

2. 封装通用函数生成过滤/连接片段 #

写一个字符串生成函数，接收通用UDTT、目标表别名、生成类型（JOIN/WHERE条件）作为参数，自动输出对应的SQL片段：

CREATE FUNCTION dbo.GenerateFilterFragment(
    @Filters dbo.GenericFilterType READONLY,
    @TargetTableAlias NVARCHAR(128),
    @UseJoin BIT = 0
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Fragment NVARCHAR(MAX) = '';

    IF @UseJoin = 1
    BEGIN
        -- 生成多值筛选的JOIN片段
        SELECT @Fragment += CONCAT(
            'INNER JOIN (SELECT FilterValue FROM @Filters WHERE FilterColumn = ''', 
            FilterColumn, 
            ''') t ON t.FilterValue = ', 
            @TargetTableAlias, '.', 
            FilterColumn, ' '
        )
        FROM (SELECT DISTINCT FilterColumn FROM @Filters) AS cols;
    END
    ELSE
    BEGIN
        -- 生成WHERE条件片段
        SELECT @Fragment += CONCAT(
            'AND ', 
            @TargetTableAlias, '.', 
            FilterColumn, 
            ' IN (SELECT FilterValue FROM @Filters WHERE FilterColumn = ''', 
            FilterColumn, 
            ''') '
        )
        FROM (SELECT DISTINCT FilterColumn FROM @Filters) AS cols;
    END

    -- 移除WHERE片段开头多余的AND
    IF @UseJoin = 0 AND LEN(@Fragment) > 0
        SET @Fragment = STUFF(@Fragment, 1, 4, '');

    RETURN @Fragment;
END

调用时直接传入参数即可，不用在每个存储过程中重复写CASE判断计数的逻辑：

DECLARE @Filters dbo.GenericFilterType;
INSERT INTO @Filters VALUES ('SomeKey', 'Value1'), ('SomeKey', 'Value2');

DECLARE @WhereFragment NVARCHAR(MAX) = dbo.GenerateFilterFragment(@Filters, 'tbl', 0);
DECLARE @JoinFragment NVARCHAR(MAX) = dbo.GenerateFilterFragment(@Filters, 'tbl', 1);

3. 用参数化动态SQL替代分支判断 #

不管UDTT参数里是1个还是多个值，统一用INNER JOIN关联表值参数，SQL Server查询优化器会自动处理单值场景的执行计划，性能不会比单独写=差。这样可以完全省去计数、赋值单变量的步骤：

CREATE PROCEDURE dbo.SomeProcedure
    @importantParam1 dbo.SomeType READONLY,
    @otherParam dbo.OtherType READONLY
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'
        SELECT *
        FROM tbl
        INNER JOIN @importantParam1 t1 ON t1.[value] = tbl.SomeKey
        INNER JOIN @otherParam t2 ON t2.[value] = tbl.OtherKey
        WHERE 1=1
    ';

    EXEC sp_executesql @sql,
        N'@importantParam1 dbo.SomeType READONLY, @otherParam dbo.OtherType READONLY',
        @importantParam1 = @importantParam1,
        @otherParam = @otherParam;
END

这种方式既避免了“厨房水槽”问题，又消除了重复的分支判断代码。

4. 用代码生成脚本减少手动重复 #

如果必须保留原有UDTT结构，可以写一个T-SQL脚本，输入UDTT名称、参数名、目标列名，自动生成需要添加到存储过程的代码块：

DECLARE @UDTTName NVARCHAR(128) = 'SomeType',
        @ParamName NVARCHAR(128) = '@importantParam1',
        @TargetColumn NVARCHAR(128) = 'SomeKey';

SELECT CONCAT(
    'DECLARE @nrOf', SUBSTRING(@ParamName, 2, LEN(@ParamName)-1), ' INT = 0;', CHAR(10),
    'DECLARE @single', SUBSTRING(@ParamName, 2, LEN(@ParamName)-1), ' NVARCHAR(35);', CHAR(10),
    'SELECT @nrOf', SUBSTRING(@ParamName, 2, LEN(@ParamName)-1), ' = COUNT(*) FROM ', @ParamName, ';', CHAR(10),
    'IF (@nrOf', SUBSTRING(@ParamName, 2, LEN(@ParamName)-1), ' = 1)', CHAR(10),
    '    SELECT @single', SUBSTRING(@ParamName, 2, LEN(@ParamName)-1), ' = [value] FROM ', @ParamName, ';', CHAR(10), CHAR(10),
    '-- 拼接片段', CHAR(10),
    'SET @joinQuery += CASE WHEN @nrOf', SUBSTRING(@ParamName, 2, LEN(@ParamName)-1), ' > 1 THEN ''INNER JOIN ', @ParamName, ' t ON t.[value] = tbl.', @TargetColumn, ''' ELSE '''' END;', CHAR(10),
    'SET @filterQuery += CASE WHEN @nrOf', SUBSTRING(@ParamName, 2, LEN(@ParamName)-1), ' = 1 THEN ''AND tbl.', @TargetColumn, ' = @single', SUBSTRING(@ParamName, 2, LEN(@ParamName)-1), ''' ELSE '''' END;'
) AS GeneratedCode;

新增UDTT时，运行脚本替换参数就能生成对应代码，减少手动编写的重复劳动。

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