为多审计字段创建动态SQL对比IAM与IAM_audit表差异

Hey there! I totally get that using cursors to compare 20+ audit field last modified dates between IAM and IAM_audit is slogging along—let’s swap that slow row-by-row approach for a snappy dynamic SQL solution that handles all fields in a single set-based query.

Here’s a step-by-step implementation tailored to your scenario, including your temporary table setup:

1. First, set up your temporary table (as provided) #

CREATE TABLE #Iam ( 
    Accnum int, 
    invnumber int, 
    name varchar(10), 
    Ac_status varchar(10)
    -- Add any other fields you need here
);

-- Insert your sample data (expand with your actual insert logic)
INSERT INTO #Iam (Accnum, invnumber, name, Ac_status)
VALUES 
    (1, 1001, 'Alice', 'Active'),
    (2, 1002, 'Bob', 'Inactive');

2. Dynamic SQL to compare all audit fields #

This script will generate a query that compares each audit field’s last modified date between the two tables, unions all results, and only returns rows where there’s a difference (you can tweak that if needed):

DECLARE @DynamicSQL NVARCHAR(MAX) = '';
DECLARE @AuditFields TABLE (FieldName NVARCHAR(100));

-- Populate this list with ALL 20 of your audit fields
INSERT INTO @AuditFields (FieldName)
VALUES 
    ('name_LastModified'),   -- Example audit field 1
    ('Ac_status_LastModified'); -- Example audit field 2
    -- Add your remaining 18 audit fields here...

-- Build the dynamic SQL query
SELECT @DynamicSQL = @DynamicSQL + 
N'
SELECT 
    ''' + af.FieldName + ''' AS AuditFieldName,
    i.Accnum,
    i.invnumber,
    i.' + af.FieldName + ' AS IAM_LastModifiedDate,
    ia.' + af.FieldName + ' AS IAM_Audit_LastModifiedDate,
    DATEDIFF(DAY, i.' + af.FieldName + ', ia.' + af.FieldName + ') AS DateDifferenceInDays
FROM #Iam i
INNER JOIN IAM_audit ia 
    ON i.Accnum = ia.Accnum 
    AND i.invnumber = ia.invnumber
WHERE i.' + af.FieldName + ' <> ia.' + af.FieldName + ' -- Only return records with differences
' + CASE WHEN LEAD(af.FieldName) OVER (ORDER BY af.FieldName) IS NOT NULL THEN 'UNION ALL' ELSE '' END
FROM @AuditFields af;

-- Execute the generated query
EXEC sp_executesql @DynamicSQL;

Key Benefits & Tweaks #

• Speed: This uses set-based operations instead of cursors, which is way faster for large datasets—no more looping through each field one by one.
• Scalability: Just add more field names to the @AuditFields table variable to include all 20 of your audit fields; no other code changes needed.
• Flexibility:
  • Remove the WHERE clause if you want to see all records (even those with matching dates).
  • Adjust DATEDIFF(DAY, ...) to use HOUR, MINUTE, or SECOND if you need finer-grained difference calculations.
  • If your audit field naming convention is different, just update the values in the INSERT INTO @AuditFields block.

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