CODEWITHSUNDEEP
sqlconditional-statementsexists
EdGruberman
EdGruberman

Reputation: 167

SQL Conditional Column Existence

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?

Upvotes: 5

Views: 5173

Answers (3)

ArBR
ArBR

Reputation: 4082

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

Upvotes: 4

bernd_k
bernd_k

Reputation: 11966

The best you can do is

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

Upvotes: 1

Marcelo Cantos
Marcelo Cantos

Reputation: 185862

No, this isn't possible. The column-access has to be compiled, if nothing else, and this happens before the expression that suppresses it is evaluated. You need to generate SQL on-the-fly for this.

Upvotes: 1

Related Questions