Home Forums SQL Server 2008 T-SQL (SS2K8) comparison in the merge statement about null values RE: comparison in the merge statement about null values

  • Paul do you have a similar trick that will work in the output clause? Output statements do not allow subqueries 🙁

    I have a store procedure that is called with a row of values and invokes a merge statement. I capture the output of the merge statement into a temp table and I want to check if any value has changed, if so I create an audit.

    Right now I use this contraption, but ideally I'd like to simplify it as I find the isnull(nullif statement horribly ugly:

    output

    inserted.$identity [id],

    convert(binary(1),isnull(nullif(inserted.colName1,deleted.colName1),nullif(deleted.colName1,inserted.colName1))),

    convert(binary(1),isnull(nullif(inserted.colName2,deleted.colName2),nullif(deleted.colName2,inserted.colName2)))

    into @ai(id,colName1,colName2)

    ...

    insert into audit(TableName,attribute,Value,Identifier,auditdate)

    select 'TableName',target.attribute,target.value,z.id,@auditDate

    from @ai z

    cross apply (VALUES

    ('colName1',convert(sql_variant,@colName1),z.colName1),

    ('colName2',convert(sql_variant,@colName2),z.colName2),

    ) target (attribute,value,includeIfNotNull)

    where target.includeIfNotNull is not null