SQL Server动态参数化存储过程实现可复用数据校验功能咨询

我们可以基于动态SQL+输出参数实现可复用的通用校验存储过程，适配不同表、列的校验需求，同时规避SQL注入风险。 #

完整存储过程代码 #

CREATE PROCEDURE dbo.usp_CommonDataValidation
    -- 输入参数
    @SourceTableName NVARCHAR(128),
    @SourceCheckColumn NVARCHAR(128),
    @TargetTableName NVARCHAR(128),
    @TargetCheckColumn NVARCHAR(128),
    @ErrorMsgPrefix NVARCHAR(200) = 'FAILED: ',
    -- 输出参数
    @valuesInput INT OUTPUT,
    @valuesInserted INT OUTPUT,
    @countDuplicates INT OUTPUT,
    @error_message NVARCHAR(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Sql NVARCHAR(MAX);

    -- 1. 计算源表非空唯一值数量
    SET @Sql = N'SELECT @ret = COUNT(DISTINCT ' + QUOTENAME(@SourceCheckColumn) + N') 
                 FROM ' + QUOTENAME(@SourceTableName) + N'
                 WHERE ' + QUOTENAME(@SourceCheckColumn) + N' IS NOT NULL';
    EXEC sp_executesql @Sql, N'@ret INT OUTPUT', @ret = @valuesInput OUTPUT;

    -- 2. 计算目标表唯一值数量
    SET @Sql = N'SELECT @ret = COUNT(DISTINCT ' + QUOTENAME(@TargetCheckColumn) + N') 
                 FROM ' + QUOTENAME(@TargetTableName);
    EXEC sp_executesql @Sql, N'@ret INT OUTPUT', @ret = @valuesInserted OUTPUT;

    -- 3. 计算目标表重复值的数量（统计有多少个列值存在重复）
    SET @Sql = N'SELECT @ret = COUNT(*) 
                 FROM (
                    SELECT ' + QUOTENAME(@TargetCheckColumn) + N'
                    FROM ' + QUOTENAME(@TargetTableName) + N'
                    GROUP BY ' + QUOTENAME(@TargetCheckColumn) + N'
                    HAVING COUNT(*) > 1
                 ) AS t';
    EXEC sp_executesql @Sql, N'@ret INT OUTPUT', @ret = @countDuplicates OUTPUT;

    -- 4. 生成错误消息
    SET @error_message = @ErrorMsgPrefix + N'源表唯一值数=' + CAST(@valuesInput AS NVARCHAR(20)) 
                        + N',目标表唯一值数=' + CAST(@valuesInserted AS NVARCHAR(20))
                        + N',目标表重复值数=' + CAST(@countDuplicates AS NVARCHAR(20));
END
GO

调用示例 #

你可以在其他存储过程中直接调用该通用校验过程，传入对应的表名和列名即可：

-- 示例1：校验employee表name列到目标表的对应字段
DECLARE @v_input INT, @v_inserted INT, @v_dup INT, @err NVARCHAR(500);
EXEC dbo.usp_CommonDataValidation
    @SourceTableName = 'employee',
    @SourceCheckColumn = 'name',
    @TargetTableName = 'TABLE_2', -- 替换为实际目标表名
    @TargetCheckColumn = 'COLUMN_2', -- 替换为实际目标列名
    @valuesInput = @v_input OUTPUT,
    @valuesInserted = @v_inserted OUTPUT,
    @countDuplicates = @v_dup OUTPUT,
    @error_message = @err OUTPUT;

-- 调用后直接使用返回的变量做后续逻辑
SELECT @v_input AS valuesInput, @v_inserted AS valuesInserted, @v_dup AS countDuplicates, @err AS error_message;

-- 示例2：校验orders表product列到另一目标表的对应字段
DECLARE @v_input INT, @v_inserted INT, @v_dup INT, @err NVARCHAR(500);
EXEC dbo.usp_CommonDataValidation
    @SourceTableName = 'orders',
    @SourceCheckColumn = 'product',
    @TargetTableName = 'another_target_table',
    @TargetCheckColumn = 'product_code',
    @valuesInput = @v_input OUTPUT,
    @valuesInserted = @v_inserted OUTPUT,
    @countDuplicates = @v_dup OUTPUT,
    @error_message = @err OUTPUT;

关键说明 #

• 所有表名、列名均使用QUOTENAME函数转义，避免特殊字符或恶意标识符导致的SQL注入问题
• 采用sp_executesql执行动态SQL，支持参数的输入输出，性能优于直接EXEC执行拼接字符串
• 优化了原重复值计数逻辑：原写法如果存在多个重复列值会返回多行导致赋值报错，改为嵌套子查询统计重复值的总数量，逻辑更稳定
• 所有输出参数可以直接在调用方的存储过程中使用，无需额外处理

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