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

  • 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
    Wednesday, March 10, 2010 2:18 PM

Answers

  • 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!
    • Marked as answer by Ralph Twele Wednesday, March 10, 2010 3:25 PM
    Wednesday, March 10, 2010 2:49 PM
  • 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
    • Marked as answer by Ralph Twele Wednesday, March 10, 2010 3:25 PM
    Wednesday, March 10, 2010 2:41 PM

All replies


  • 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
    Wednesday, March 10, 2010 2:37 PM
  • This sounds like very poor design.

    You will have to have 2 procedures.
    Dave Ballantyne ---- http://sqlandthelike.blogspot.com
    Wednesday, March 10, 2010 2:41 PM
  • 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
    • Marked as answer by Ralph Twele Wednesday, March 10, 2010 3:25 PM
    Wednesday, March 10, 2010 2:41 PM
  • 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!
    • Marked as answer by Ralph Twele Wednesday, March 10, 2010 3:25 PM
    Wednesday, March 10, 2010 2:49 PM
  • 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
    Wednesday, March 10, 2010 2:58 PM
  • It gets a little bit confusing. My previous post was the reply to Naom.
    Ralph Twele
    Wednesday, March 10, 2010 3:00 PM
  • 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

    Wednesday, March 10, 2010 3:02 PM
  • 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
    Wednesday, March 10, 2010 3:06 PM
  • 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
    Wednesday, March 10, 2010 3:24 PM