SQL conditional column existence

Question

Is there any way to select a column if it exists in the view, but ignore the column if it does not exist?

SELECT
    CASE
        WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyView' AND COLUMN_NAME = 'MyColumn')
            THEN MyView.MyColumn
        ELSE NULL
    END AS [Sometimes]
FROM
    MyView

This currently returns the error "Msg 207 Invalid column name".

Perhaps there is an opportunity to ignore this error?

Answer 1

You can do this using dynamic SQL :

declare @sql varchar(200)   
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyView' AND COLUMN_NAME = 'MyColumn') 
BEGIN
    select  @sql = "SELECT Column1m Column2, MyColum from MyView"
END
ELSE
BEGIN
    select  @sql = "SELECT Column1m Column2, null as MyColum from MyView"
END 

-- executes dynamic sql
EXEC @sql

Answer 2

No, It is Immpossible. The access column must be compiled if nothing else, and this happens before the expression that suppresses it is evaluated. To do this, you need to generate SQL on the fly.

Answer 3

, ,

if EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyView' AND COLUMN_NAME = 'MyColumn')
Select MyView.MyColumn from MyView
else
Select NULL MyColumn