Creating Dynamic Column List

Scenario:

Wish to query a table to see which rows have changed (basically an audit on the table) so the query involves a self join. The where clause needs to be created based on the columns available in the table - we can do this dynamically...

This solution was also modified to generate a Case statement using the columns in a table dynamically (solution 2)

Solution 1:

Nice piece of code... creates the WHERE clause of a dynamic query using the columns on the table

DECLARE @match_str nvarchar(max)

SET @match_str = '(' + CHAR(10) + '0=1' + CHAR(10)

-- selects our comparison column namesSELECT @match_str = @match_str + 'OR '
+ 'dd.[' + c.[name] + '] != ii.[' + c.[name] + '] OR ' + '(dd.[' + c.[name] + '] IS NULL AND ii.[' + c.[name] + '] IS NOT NULL) OR ' + '(dd.[' + c.[name] + '] IS NOT NULL AND ii.[' + c.[name] + '] IS NULL)' + CHAR(10)
FROM  sys.columns c
LEFT JOIN [develop].[internal_fields] fi ON fi.[field_name] = c.[name]
LEFT JOIN [meta].[audit_internal_udfs] iu ON iu.[column_name] = c.[name]
WHERE c.[object_id] = OBJECT_ID( 'dbo.TradeEntry') AND c.[name] != 'TradeEntryId' AND c.[name] NOT IN ( 'CreateDate', 'ChangeDate', 'RowVersion') AND c.system_type_id != 241 AND fi.[_identity] IS NULL AND
iu.[_identity] IS NULL

SET @match_str = @match_str + ')'

select '@match_str: provides column joins for comparisons to find changed fields later on'

select @match_str

 

Solution 2:

SELECT c.[name],column_id, 'when ''' + c.[name] + ''' then cast(a1.' + c.[name] + ' as varchar)', 'when ''' + c.[name] + ''' then cast(a2.' + c.[name] + ' as varchar)'
FROM  sys.columns c
LEFT JOIN [develop].[internal_fields] fi ON fi.[field_name] = c.[name]
LEFT JOIN [meta].[audit_internal_udfs] iu ON iu.[column_name] = c.[name]
WHERE c.[object_id] = OBJECT_ID( 'dbo.TradeEntry') AND
c.[name] != 'TradeEntryId' AND
c.[name] NOT IN ( 'CreateDate', 'ChangeDate', 'RowVersion', 'ProcessingDateTime') AND
c.system_type_id != 241 AND
fi.[_identity] IS NULL AND iu.[_identity] IS NULL

declare @caseStatement1 varchar(max)
select @caseStatement1 = coalesce(@caseStatement1 + ' ' + caseStatement1, 'case (colName) ' + caseStatement1)
from @cols