0

I'm trying to do a t-sql query that spans multiple DBs on the same SQL instance (running on the 'master' and doing a union all). It's dynamically built but the problem I have is some databases have been updated to include the new column, but some have not.

I know logically my statement should work but SQL keeps throwing an error saying the column doesn't exist and won't even try to run it (which is the whole point of the IIF() or just IF statment).

I've tried multiple versions of this (both col_length() and exists() but nothing is making a difference. How do I do this? This query works IF the column exists, but falls apart if it doesn't.

select 'Test' as dbName, branches.branchName
            from test.dbo.branches
                where branches.headOffice = 1
                    and 'STRP' = IIF(col_length('test.dbo.branches', 'pmtService') is not null, branches.pmtService, '')

Sql error:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'pmtService'.

I almost just need an eval(), like

IIF(col_length('test.dbo.branches', 'pmtService') is not null, eval('branches.pmtService'), '')

but I know that's not an option in tSQL. Any ideas?

ScottR
  • 187
  • 1
  • 1
  • 13
  • This has a kind of code smell about it, but you'll need to dynamically build your SQL and exec() it - which is kind of synonymous with eval. – Stu Jul 11 '23 at 21:02
  • One TSQL eval, coming right up: https://stackoverflow.com/a/8454706/6567275 – Thomas Jul 11 '23 at 21:20
  • another thread might answer your question https://dba.stackexchange.com/questions/66741/why-cant-i-use-a-case-statement-to-see-if-a-column-exists-and-not-select-from-i/66755#66755 – Power Mouse Jul 12 '23 at 13:30
  • 1
    Thanks @PowerMouse. That cross apply worked. I had no idea it would fill in the value like that with the from (select null as pmtService) as placeHolder – ScottR Jul 13 '23 at 13:00

0 Answers0