Using column value if column exist

Question

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

HoneyBadger · Accepted Answer · 2017-07-07 16:45:45Z

4

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

edited Jul 7, 2017 at 16:45

answered Jul 7, 2017 at 9:02

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Oscar · Accepted Answer · 2017-07-07 08:58:05Z

0

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

answered Jul 7, 2017 at 8:58

Oscar

14.1k9 gold badges53 silver badges84 bronze badges

1 Comment

huMpty duMpty Over a year ago

Sorry, forgot to mention in question. I tried this as well but with same result

Collectives™ on Stack Overflow

Using column value if column exist

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related