使用LAG/ROW_NUMBER生成审计视图列名、新旧值的类型转换错误问题

核心问题根源 #

你遇到的Msg 295错误是因为UNION ALL合并结果集时，SQL Server会尝试对不同数据类型的列做隐式转换，而smalldatetime与字符串类型的隐式转换规则不兼容导致的。解决的关键是将所有字段的新旧值统一转换为字符串类型（推荐用NVARCHAR(MAX)，兼容所有数据类型）。

实现方案 #

假设你的遗留Audit表包含操作类型标识（如OperationType，值为INSERT/UPDATE），以及每个业务字段对应的旧值列（如Old_XXX对应XXX），以下是通用的视图创建逻辑：

示例SQL代码

CREATE VIEW AuditDetailView
AS
-- 处理ID字段（INT类型）
SELECT 
    'ID' AS COLUMN_NAME,
    CASE WHEN OperationType = 'UPDATE' THEN CAST(Old_ID AS NVARCHAR(MAX)) ELSE NULL END AS OLD_VALUE,
    CAST(ID AS NVARCHAR(MAX)) AS NEW_VALUE,
    AuditID -- 保留原审计记录ID用于关联
FROM LegacyAudit
WHERE OperationType = 'INSERT' 
   OR (OperationType = 'UPDATE' AND (Old_ID <> ID OR (Old_ID IS NULL AND ID IS NOT NULL) OR (Old_ID IS NOT NULL AND ID IS NULL)))

UNION ALL

-- 处理Name字段（NVARCHAR类型）
SELECT 
    'Name' AS COLUMN_NAME,
    CASE WHEN OperationType = 'UPDATE' THEN Old_Name ELSE NULL END AS OLD_VALUE,
    Name AS NEW_VALUE,
    AuditID
FROM LegacyAudit
WHERE OperationType = 'INSERT' 
   OR (OperationType = 'UPDATE' AND (Old_Name <> Name OR (Old_Name IS NULL AND Name IS NOT NULL) OR (Old_Name IS NOT NULL AND Name IS NULL)))

UNION ALL

-- 处理CreateDate字段（SMALLDATETIME类型）
SELECT 
    'CreateDate' AS COLUMN_NAME,
    CASE WHEN OperationType = 'UPDATE' THEN CONVERT(NVARCHAR(MAX), Old_CreateDate, 120) ELSE NULL END AS OLD_VALUE,
    CONVERT(NVARCHAR(MAX), CreateDate, 120) AS NEW_VALUE,
    AuditID
FROM LegacyAudit
WHERE OperationType = 'INSERT' 
   OR (OperationType = 'UPDATE' AND (Old_CreateDate <> CreateDate OR (Old_CreateDate IS NULL AND CreateDate IS NOT NULL) OR (Old_CreateDate IS NOT NULL AND CreateDate IS NULL)))

关键注意事项 #

• 统一数据类型：所有字段的OLD_VALUE和NEW_VALUE都转换为NVARCHAR(MAX)，彻底避免类型不兼容问题。
• 日期类型格式化：用CONVERT指定日期格式（如120对应yyyy-mm-dd hh:mi:ss），确保日期字符串的可读性和一致性。
• NULL值处理：判断字段差异时要覆盖NULL值场景，避免遗漏新旧值一方为NULL的更新记录。
• 按需扩展：对Audit表中的每个业务字段，复制上述UNION ALL块并替换对应的字段名即可。

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