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