Ambiguous column name when select a column to return value if column is available in table by Execute Dynamic SQL commands

Question

I want to return a column value if column is available in table,if not, return a default value, then I face COLUMN_NAME ambiguous error when join two table SHAIN1 and RIREKI14. If select from only one table then query works ok but if I join two table, I face problem.

declare @sql nvarchar(max) = ' SELECT 1 as id, '+ (case when exists (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA ='dbo' and TABLE_NAME='RIREKI14' and COLUMN_NAME='KOM001') then 'KOM001' else 'NULL' end) + ' as day ' + ' From RIREKI14 join SHAIN1 on RIREKI14.INCODE = SHAIN1.INCODE  '; exec sp_executesql @sql

Help me please!

Answer 1

This is your logic:

declare @sql nvarchar(max) = '
SELECT 1 as id, '+
    (case when exists (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA  = 'dbo' and TABLE_NAME = 'RIREKI14' and COLUMN_NAME = 'KOM001')
          then 'KOM001'
          else 'NULL'
     end) + ' as day ' + '
From RIREKI14 join
     SHAIN1
     on RIREKI14.INCODE = SHAIN1.INCODE
';

exec sp_executesql @sql;

The only possibility for an ambiguous column name is the name coming from the case. So, let's qualify it:

declare @sql nvarchar(max) = '
SELECT 1 as id, '+
    (case when exists (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA  = 'dbo' and TABLE_NAME = 'RIREKI14' and COLUMN_NAME = 'KOM001')
          then 'r.KOM001'
          else 'NULL'
     end) + ' as day ' + '
From RIREKI14 r join
     SHAIN1 s
     on r.INCODE = s.INCODE
';

exec sp_executesql @sql;