求助：如何用SQL Server查询/存储过程验证数据仓库表列有效值

Got it，我来帮你搞定这个SQL Server数据校验的需求！首先得先明确你的规则表（也就是你说的Table1里的ColumnsToValidate）的结构，通常这类表用来存要校验的目标表、列，以及对应的有效规则。我先给你一套通用的实现方案，你可以根据自己的实际情况调整。

1. 先定义校验规则表（如果还没的话） #

首先我们需要一个表来存储所有要校验的表、列和对应的有效规则。假设你的规则表结构如下：

CREATE TABLE ColumnsToValidate (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    TargetTableName NVARCHAR(128) NOT NULL, -- 要校验的目标表名
    TargetColumnName NVARCHAR(128) NOT NULL, -- 要校验的目标列名
    ValidValueRule NVARCHAR(MAX) NOT NULL, -- 该列的有效取值规则（SQL条件语句）
    ValidationDescription NVARCHAR(255) -- 可选，描述这个校验的业务含义
)

然后插入你的示例校验规则，比如验证Customer表的Gender列：

INSERT INTO ColumnsToValidate (TargetTableName, TargetColumnName, ValidValueRule, ValidationDescription)
VALUES 
('Customer', 'Gender', 'IN (''M'', ''F'', ''U'')', '验证客户性别只能是M(男)、F(女)、U(未知)'),
-- 可以继续添加其他校验规则
('Order', 'OrderStatus', 'IN (''Pending'', ''Shipped'', ''Cancelled'')', '验证订单状态是否合法'),
('Product', 'Price', 'BETWEEN 0 AND 10000', '验证产品价格必须在0到10000之间')

2. 编写校验存储过程 #

接下来我们写一个存储过程，用来批量执行所有校验规则，返回所有不符合规则的记录：

CREATE PROCEDURE ValidateColumnValues
AS
BEGIN
    SET NOCOUNT ON;

    -- 创建临时表存储校验不通过的结果
    CREATE TABLE #InvalidRecords (
        ValidationID INT,
        TargetTableName NVARCHAR(128),
        TargetColumnName NVARCHAR(128),
        InvalidRecordID INT, -- 目标表的主键，用来定位无效记录（可根据实际调整）
        InvalidValue SQL_VARIANT, -- 存储无效的具体值
        ValidationRule NVARCHAR(MAX), -- 对应的校验规则
        ValidationDescription NVARCHAR(255) -- 校验规则的描述
    )

    -- 声明游标遍历所有校验规则
    DECLARE 
        @ID INT, 
        @TargetTable NVARCHAR(128), 
        @TargetCol NVARCHAR(128), 
        @Rule NVARCHAR(MAX),
        @Desc NVARCHAR(255),
        @PrimaryKey NVARCHAR(128),
        @SQL NVARCHAR(MAX)

    DECLARE ValidationCursor CURSOR FOR
        SELECT ID, TargetTableName, TargetColumnName, ValidValueRule, ValidationDescription
        FROM ColumnsToValidate

    OPEN ValidationCursor
    FETCH NEXT FROM ValidationCursor INTO @ID, @TargetTable, @TargetCol, @Rule, @Desc

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 获取目标表的主键（用来唯一标识无效记录，如果没有主键可替换成其他唯一列）
        SELECT @PrimaryKey = COLUMN_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
          AND TABLE_NAME = @TargetTable

        -- 拼接动态SQL，查询不符合规则的记录
        SET @SQL = N'
            INSERT INTO #InvalidRecords (ValidationID, TargetTableName, TargetColumnName, InvalidRecordID, InvalidValue, ValidationRule, ValidationDescription)
            SELECT 
                ' + CAST(@ID AS NVARCHAR) + N', 
                ''' + @TargetTable + N''', 
                ''' + @TargetCol + N''', 
                ' + QUOTENAME(@PrimaryKey) + N', 
                ' + QUOTENAME(@TargetCol) + N', 
                ''' + REPLACE(@Rule, '''', '''''') + N''',
                ''' + REPLACE(@Desc, '''', '''''') + N'''
            FROM ' + QUOTENAME(@TargetTable) + N'
            WHERE ' + QUOTENAME(@TargetCol) + N' NOT ' + @Rule + N'
              AND ' + QUOTENAME(@TargetCol) + N' IS NOT NULL -- 排除NULL值，如果需要校验NULL可以删除此行
        '

        -- 执行动态SQL
        EXEC sp_executesql @SQL

        FETCH NEXT FROM ValidationCursor INTO @ID, @TargetTable, @TargetCol, @Rule, @Desc
    END

    CLOSE ValidationCursor
    DEALLOCATE ValidationCursor

    -- 返回所有无效记录
    SELECT * FROM #InvalidRecords ORDER BY TargetTableName, TargetColumnName

    DROP TABLE #InvalidRecords
END

3. 执行校验并查看结果 #

直接调用存储过程就能获取所有不符合规则的记录了：

EXEC ValidateColumnValues

• 主键适配：如果你的目标表没有主键，需要调整InvalidRecordID的取值逻辑，可以用其他唯一标识列（比如唯一索引列），或者多个列组合成唯一标识。
• 规则灵活性：ValidValueRule支持任何合法的SQL条件，比如正则校验可以写PATINDEX(''^[A-Z0-9]+$'', ' + QUOTENAME(@TargetCol) + N') = 0，或者判断是否为空的规则IS NULL。
• SQL注入防护：这里用了QUOTENAME来处理表名和列名，避免SQL注入风险；如果ValidValueRule是用户输入的，一定要做语法校验，防止恶意注入。
• 定时执行：如果需要定期校验，可以把这个存储过程加到SQL Server代理作业里，定时执行并生成校验报告（比如导出到Excel或者发送邮件）。

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