SQL Server基于数据驱动的类型转换校验优化方案问询

方案1：枚举常见数据类型（简洁直接） #

T-SQL常用数据类型数量有限，可通过CASE表达式针对每个类型做TRY_CONVERT判断，将所有校验逻辑整合到WHERE子句中：

SELECT [key], [value], [type]
FROM [source_table]
WHERE [value] IS NOT NULL
AND (
    CASE [type]
        WHEN 'int' THEN TRY_CONVERT(int, [value])
        WHEN 'varchar' THEN [value] -- varchar无需转换，直接判定为有效
        WHEN 'date' THEN TRY_CONVERT(date, [value])
        WHEN 'datetime' THEN TRY_CONVERT(datetime, [value])
        WHEN 'decimal' THEN TRY_CONVERT(decimal, [value])
        WHEN 'bit' THEN TRY_CONVERT(bit, [value])
        -- 按需添加其他你用到的数据类型
        ELSE [value] -- 未枚举的类型默认视为有效，也可改为NULL标记为无效
    END IS NULL
)

优点：无需动态SQL，语句直观易维护，适合类型数量不多的场景。
缺点：需要手动枚举所有用到的数据类型，新增类型时需同步更新CASE分支。

方案2：自动生成动态SQL（扩展性好） #

如果涉及的数据类型较多，可通过查询系统视图sys.types自动生成各类型的校验语句，拼接成完整查询，避免手动编写每个类型的分支：

DECLARE @sql NVARCHAR(MAX)

SELECT @sql = STRING_AGG(
    N'
    SELECT [key], [value], [type]
    FROM [source_table]
    WHERE [type] = ''' + name + '''
      AND [value] IS NOT NULL
      AND TRY_CONVERT(' + name + ', [value]) IS NULL',
    N' UNION ALL '
)
FROM sys.types
WHERE name IN (SELECT DISTINCT [type] FROM [source_table]) -- 仅针对表中存在的类型生成逻辑
  AND is_user_defined = 0 -- 仅包含系统内置类型，有自定义类型可去掉此条件

EXEC sp_executesql @sql

优点：自动适配表中存在的所有数据类型，无需手动维护类型列表，扩展性强。
缺点：本质还是动态SQL，但通过系统视图自动生成，比手动编写繁琐分支简洁很多。

补充说明 #

• 对于varchar、nvarchar这类字符串类型，只要value不为NULL即可判定为有效，无需转换校验。
• 若存在自定义数据类型，可调整方案2中sys.types的过滤条件，加入自定义类型的判断逻辑。

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