Case expression to check if column exists throws invalid column name error

Question

I'm building a Stored Procedure meant to be run daily. Due to how the data is provided me, some days some of the columns necessary for the output are not in the file that's imported to a temporary table.

For the output, the value can be null/empty, but the actual column has to be there.

I've tried the following code:

select

case

when exists(
        select * from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = 'tableName' and COLUMN_NAME = 'ABC'
    ) then ABC
                
else ''                 
end 
as 'XYZ' from tableName

I know for a fact that in the tests I'm running the column DOES NOT exist, so I'm expecting the SELECT statment to simply return an empty string for column XYZ.

However when running such SELECT statement, I get the following error:

Invalid column name 'ABC'.

Since I know from the start that the column ABC does not exist, I was expecting that the EXISTS(...) would evaluate to FALSE and jump straight to the ELSE statement. However it seems that the column name is still evaluated.

How can I get around this?

T-SQL is a **compiled** language, you can't use a `CASE` **expression** to check if a column exists and then refer to it only if it does; when the statement/batch is compiled the parser sees the reference to the invalid column `ABC` and generates an error. To "get around" the issue, honestly, I would ensure that your tables have a consistent definition. Assuming that you are importing this data in some kind of ETL process, then have that ETL process create *all* the columns (you need), regardless of if the data arrives with those columns or not. — Thom A, Jan 30 '23 at 11:33
One workaround is 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 — Martin Smith, Jan 30 '23 at 11:34
Also, I emphasised **expression** in `CASE` expression as you referred to it as a statement in your question (I've since edited that). `CASE` is *not* a logical flow operator in SQL Server, like a `Switch` statement in C#. `CASE` is an expression that returns a scalar value. You *can't* put logical flow operators (like `IF`) inside a statement in T-SQL. — Thom A, Jan 30 '23 at 11:51
I have no way of changing how the data is structured. The data comes from an external provider as is. I've tried the workaround by @MartinSmith but I'm now getting an error saying that the subquery returns more than one value. I've also tried to use a WHERE clause to only get the value for a specific identifier, but then i get another error saying that 'Only one expression can be specified in the select list when the subquery is not introduced with EXISTS'. — pontedm, Jan 30 '23 at 12:09
You've misapplied the method in the linked answer then. As written there it doesn't give that error. https://dbfiddle.uk/Kx1pBkWb I have no idea how you have applied it so can't tell you how to fix it — Martin Smith, Jan 30 '23 at 12:12
If you're dealing with variable data structures, T-SQL makes for a poor ETL language, as it very much wants fixed structures. Clever workarounds in T-SQL notwithstanding, this will be much less painful to deal with (in terms of writing code and maintaining it) in dedicated ETL tooling. — Jeroen Mostert, Jan 30 '23 at 12:49

score 1 · Accepted Answer · answered Jan 30 '23 at 12:55

I believe i have solved my troubles thanks to the workaround provided by @MartinSmith (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)

I ended up using an alternative version of the workaround provided, linked by the poster of @MartinSmith's solution, but the results seem to be what i was expecting.

Regarding the Only one expression can be specified in the select list when the subquery is not introduced with EXISTS error, it was being caused by me trying to SELECT two columns in the subquery. Solution came from here: https://stackoverflow.com/a/7684626/18191554

Now, my code is as follows:

select
.
.
.
.
(select (SELECT [ABC]
           from dbo.tableName 
           where ID = temp.ID])
           from (select '' as [ABC]) as dummy)
           
        as 'XYZ',

.
.
.
from tableName

where ABC is the column that may or may not exist.

Thanks to everyone involved

Case expression to check if column exists throws invalid column name error

1 Answers1