CODEWITHSUNDEEP
sqlsql-server-2008-r2
huMpty duMpty
huMpty duMpty

Reputation: 14470

Using column value if column exist

I am trying to select the values from column, if the column exist.

This table is going under some modification and some columns are removed and values are extracted to another table.

Query I am using is below. At this stage column can be presented or may be already dropped, so I am doing a column check

IF COL_LENGTH('MyTableName', 'MyColumnName') IS NOT NULL
BEGIN
    INSERT INTO SOMETABLE VALUES(TITLE,TYPEID)
    SELECT DISTINCT MyColumnName,2
    FROM MyTableName
END

Also tried

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'MyColumnName'
          AND Object_ID = Object_ID(N'dbo.MyTableName'))
BEGIN
    INSERT INTO SOMETABLE VALUES(TITLE,TYPEID)
    SELECT DISTINCT MyColumnName,2
    FROM MyTableName
END

But this give me error when the Column is dropped even with the column exist check

Invalid column name 'MyColumnName'

Is there a way to get around this

Upvotes: 2

Views: 156

Answers (2)

HoneyBadger
HoneyBadger

Reputation: 15150

I suspect the error originates during the compiling of the query. The compiler doesn't consider IF statements, it evaluates the whole query as if everything is to be executed. When a non-existing column is encountered you get an error, regardless of whether you checked for existence. I think the best solution is to use dynamic SQL to get around that.

Something like:

IF COL_LENGTH('MyTableName', 'MyColumnName') IS NOT NULL
BEGIN
    EXEC sys.sp_executesql N'INSERT INTO SOMETABLE VALUES(TITLE,TYPEID)
                             SELECT DISTINCT MyColumnName,2
                             FROM MyTableName'
END

Upvotes: 4

Oscar
Oscar

Reputation: 13990

Try with:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'MyColumnName'
          AND Object_ID = Object_ID(N'dbo.MyTableName'))
BEGIN
    INSERT INTO SOMETABLE VALUES(TITLE,TYPEID)
    SELECT DISTINCT MyColumnName,2
    FROM MyTableName
END

Upvotes: 0

Related Questions