Take default value if column doesn't exist

[indian143]

Hi All, I am writing a Dynamic Sql in a while loop which loop through all Tables and then columns in ever table, I have written a general Dynamic sql to read the columns but some tables do not have CreatedBy and ModifiedBy columns and when it is getting executed the Dynamic Sql is failing for some of the scenarios. Can somebody suggest me how to write that Dynamic Sql to fit for both the scenarios. Any suggestion is helpful thanks in advance. Here is my Dynamic sql

	SET @sql =N'INSERT INTO TrackUpdatedColumnNamesFlatTable (TableName, ColumnName, PrimaryKeyColumnName, PrimaryKeyValue, TransactionId
		, TransactionType, CreatedBy, ModifiedBy, CreatedDate, ModifiedDate, IsSoftDelete, IsCmsActive, ColumnValueAfter)
		select ''' + @TblName + ''', ''' +  @ColName + ''', ''' +  @PKColName + ''' 
		, ' +  @PKColName + ', \_\_$start\_lsn	, CASE WHEN (\_\_$operation=1) THEN ''Delete''
		WHEN \_\_$operation=2 THEN ''Insert''
		WHEN \_\_$operation=4 THEN ''Update''
		END	TransactionType, CreatedBy, ModifiedBy, CreatedDate, ModifiedDate
		, ' + CAST(@IsDeleted AS varchar(max)) + ' 
		, ' + CAST(@IsCmsActive AS varchar(max)) + ' 
		, CASE WHEN \_\_$operation=1 THEN NULL ELSE CAST(' + @ColName + ' AS nvarchar(max)) END 			
	FROM \[cdc\].' + @cdcinstancename 
	+ ' WHERE CAST(sys.fn\_cdc\_map\_lsn\_to\_time(\_\_$start\_lsn) AS DATE)=CAST(''' + @DateToTrack + ''' AS DATE)
	AND sys.fn\_cdc\_is\_bit\_set(sys.fn\_cdc\_get\_column\_ordinal(''' + @captureinstance +
	''',''' + '' + @ColName + '''), \_\_$update\_mask) = 1 
	AND \_\_$operation IN (1, 2, 4)';
	
	exec sp\_executesql @sql, N'@IsColumnModified int out', @IsColumnModified out

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

[CHill60]

Query the sys.columns table for @TbleName and add if the CreatedBy, ModifiedBy columns exist on that table add that in to the dynamic SQL