default value if a column doesnt exist in the SQL table

Question

I have the below sample code. Assuming I do not know if a particular column exists in a table or not, how could I write the query in a way that I can default the column value to 'Not available' if the column doesn't exist in the table?

Example:

select COL1, COL2,
CASE
    WHEN OBJECT_ID('COL3') IS NULL THEN 'Not Available'
    ELSE COL3
END AS COL3
from TABLE1

Thanks in advance.

You can't really do this with regular SQL, and in fact I'm pretty sure your above statement won't even compile unless COL3 already really exists. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 26, 2020 at 10:49
yes this sql doesnt work, its just to illustrate what i am trying to achieve here. — Newbie
– Newbie, Commented Jun 26, 2020 at 10:49
You would need to use dynamic SQL for this since a query that references a non-existent column will not compile. But I think you have larger issues if the schema is unknown at run time. — Dan Guzman
– Dan Guzman, Commented Jun 26, 2020 at 10:52

Gordon Linoff · Accepted Answer · 2020-06-26 11:09:53Z

1

This is quite tricky to do (without dynamic SQL), but there is a way by playing with the scoping rules in SQL. You can do this assuming you have a unique or primary key in the table:

select t1.col1, t1.col2,
       (select col3                               -- no alias!
        from table1 tt1
        where tt1.id = t1.id                      -- the primary/unique key
       ) col3 
from table1 t1 cross join
     (values ('Not Available')) v(col3)           -- same name

The subquery will fetch col3 from the table1 in the subquery if it exists. Otherwise it will reach out and find col3 from the values() clause.

edited Jun 26, 2020 at 11:09

answered Jun 26, 2020 at 10:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

default value if a column doesnt exist in the SQL table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related