How to prevent "Invalid column name" error for not existing column in SELECT?

Question

Hi,
I would like to have a SELECT query which tolerates if a column in the select list is missing in the database like

SELECT ExistingColumn, PotentiallyMissingColumn FROM MyTable

If the statement is executed in a DB which does not have the PotentiallyMissingColumn it should return a default value instead. I have tried

SELECT ExistingColumn,
   CASE 
      WHEN EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
                  WHERE TABLE_NAME = 'MyTable' AND 
                        COLUMN_NAME = 'PotentiallyMissingColumn') THEN PotentiallyMissingColumn
      ELSE  'DefaultValue'
   END AS PotentiallyMissingColumn
FROM MyTable

But this still produces the error "Invalid column name". I am using SQL Server 2008.
My assumption is that the error is already thrown during compilation before the statement is actually executed.
Is there a way to achieve what I need?

Regards,
Ralph

Answer 1

Ok, I have now tried

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_NAME = 'MyTable' AND COLUMN_NAME = 'PotentiallyMissingColumn')
BEGIN
	SELECT ExistingColumn, PotentiallyMissingColumn FROM MyTable
END
ELSE
BEGIN
	SELECT ExistingColumn, 'DefaultValue' FROM MyTable
END	

But the error still appears (using SSMS). In my case it is a missing column, not a missing table. Maybe that behaves differently?

Regards,
Ralph

Answer 2

This sounds like very poor design.

You will have to have 2 procedures.

---

Dave Ballantyne ---- http://sqlandthelike.blogspot.com

Answer 3

Even this construct doesn't work

if exists (select 1 from Information_Schema.Columns where Table_Name = 'myTable' and Column = 'myColumn')

   select myColumn from myTable

else
  select 'Default' as myColumn from myTable


The only way to work around is to use a separate batch (e.g. construct your query as a string and run using execute sp_ExecuteSQL). 

This is a bit complex...

      

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 4

declare @column varchar(50)
declare @sql  nvarchar(max)


if EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_NAME = 'MyTable'COLUMN_NAME = 'PotentiallyMissingColumn')
set @column='PotentiallyMissingColumn'
else
set @column='''DefaultValue'''
set @sql='SELECT ExistingColumn,'+@column+' from MyTable'

print @column 
print @sql


execute(@sql)

---

Knowledge Is The Ultimate Power!

Answer 5

You mean like this?

if exists (select 1 from Information_Schema.Columns where Table_Name = 'myTable' and Column = 'myColumn')

   exec sp_executesql 'select myColumn from myTable'

else
  select 'Default' as myColumn from myTable

This seems to work. However, I then have to (almost) duplicate the in reality very complex query, what I tried to avoid.

---

Ralph Twele

Answer 6

It gets a little bit confusing. My previous post was the reply to Naom.

---

Ralph Twele

Answer 7

Hello , The above code is working for me in SQL SERVER 2005..I just changed
if exists (select 1 from Information_Schema.Columns where Table_Name = 'myTable' and Column_NAME = 'myColumn')

Please have a test Code, seems like it works fine

Create table #T(id int, name varchar(30))
go
insert into #T values(1,'Test')
go

if Exists(
Select * from INFORMATION_SCHEMA.COLUMNS
where TAble_Name='#T__________________________________________________________________________________________________________________000000000016'

and Column_Name='id')

select id, name from #T
--Results
--1	Test
Else

Select id,name, 'mydefault' as MYCol from #T


--Test for column not Existing
if Exists(
Select * from INFORMATION_SCHEMA.COLUMNS
where TAble_Name='#T__________________________________________________________________________________________________________________000000000016'

and Column_Name='id1')

select id, name from #T
--Results
--1	Test
Else

Select id,name, 'mydefault' as MYCol from #T
--Results
--1	Test	mydefault

 

Thanks

Answer 8

Yes, that's exactly what I meant. Unfortunately, there are not many other alternatives...

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 9

Thanks to everybody for your ideas. It seems I go for Raj's proposal. It works and doesn't require to duplicate the query. Thanks a lot.

---

Ralph Twele