SQL Conditional Column Existence

Question

Can I somehow select a column if exists in a 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

Right now, that returns a "Msg 207 Invalid column name" error.

Perhaps some option to ignore that error is possible?

Answer 1

You can do it by 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 Column1 AS TheColumn1, MyColumn from MyView"
END
ELSE
BEGIN
    select  @sql = "SELECT Column1 AS TheColumn1, null AS MyColumn from MyView"
END 

-- executes dynamic sql
EXEC @sql