Column existence Check - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Column existence Check

Author

Topic

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2008-02-19 : 03:16:54

Hi,
I want to do some DML Operation based on the column existence in the table,But I get the Error and not able to figure out why.

IF EXISTS(SELECT * FROM SYS.COLUMNS
WHERE [name]='COL3' AND OBJECT_ID=OBJECT_ID('ColumnExistenceCheck'))
BEGIN
PRINT'COL3 EXISTS'
INSERT INTO ColumnExistenceCheck(COL3)
SELECT COL3 FROM ColumnExistenceCheck
END
ELSE
BEGIN
INSERT INTO ColumnExistenceCheck(COL2)
SELECT 2
END

Msg 207, Level 16, State 1, Line 8
Invalid column name 'COL3'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'COL3'.

I have the existence check COL3. IF COL3 Exists,then only I go to begin..end Block..In this scenario,the COL3 does not exist..Still SQL Server validates the column names in the Table which throws the Error.

Any help would be highly appreciated.

Thanks,
Raju

Thanks!
Raju
http://rajusqlblog.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-19 : 03:35:10

If col_length('table_name','column_name') is null
--column doesnt exist
else
--column exists

Madhivanan

Failing to plan is Planning to fail

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2008-02-19 : 03:51:59

quote:
Originally posted by vgr.raju

Hi,
I want to do some DML Operation based on the column existence in the table,But I get the Error and not able to figure out why.

IF EXISTS(SELECT * FROM SYS.COLUMNS
WHERE [name]='COL3' AND OBJECT_ID=OBJECT_ID('ColumnExistenceCheck'))
BEGIN
PRINT'COL3 EXISTS'
INSERT INTO ColumnExistenceCheck(COL3)
SELECT COL3 FROM ColumnExistenceCheckEND
ELSE
BEGIN
INSERT INTO ColumnExistenceCheck(COL2)
SELECT 2
END

Msg 207, Level 16, State 1, Line 8
Invalid column name 'COL3'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'COL3'.

I have the existence check COL3. IF COL3 Exists,then only I go to begin..end Block..In this scenario,the COL3 does not exist..Still SQL Server validates the column names in the Table which throws the Error.

Any help would be highly appreciated.

Thanks,
Raju

Thanks!
Raju
http://rajusqlblog.blogspot.com/

I really didn't get, what exactly u wants to do here? Why r u selecting the value and again inserting the same in the same table? whats the use of it?

Let us know, what u wants to do?

Mahesh

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2008-02-19 : 07:26:33

Hi Madhivanan,
Thanks for the reply.
When I try using col_length function, it throws the same error message.
If col_length('ColumnExistenceCheck','COL3') is not null
BEGIN
PRINT'COL3 EXISTS'
INSERT INTO ColumnExistenceCheck(COL3)
SELECT COL3 FROM ColumnExistenceCheck
END

ELSE
BEGIN
INSERT INTO ColumnExistenceCheck(COL2)
SELECT 2
END

Msg 207, Level 16, State 1, Line 5
Invalid column name 'COL3'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'COL3'.

We are doing the database updgrade step. These columns are going to be dropped in few test environment and I want my script to handle the existence and non-existence of these columns.

Thanks,
Raju

Thanks!
Raju
http://rajusqlblog.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-19 : 07:36:14

I think it is parser error. Make it as procedure and run
But still dont know what you are trying to do

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 07:36:38

Make sure you have not created same table under different schemas. Prefix schema name to the table name while checking like:

select col_length('dbo.ColumnExistenceCheck','COL3')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2008-02-19 : 07:49:45

I have referenced the schema and the same issue.
The issue for the problem is "I am referencing the invalid column name"
in the select statement. But this shouldn't even go inside that block because the if condition returns false. Eventhough IF condition return false, SQL Server somehow tries to validate the columns and throws the Error.

If I don't have the invalid column reference in select statement,then things work fine..
Any help would be much appreciated..

If col_length('ColumnExistenceCheck','COL3') is not null
BEGIN
PRINT'COL3 EXISTS'
INSERT INTO ColumnExistenceCheck(COL3)
SELECT COL3 FROM ColumnExistenceCheck
END

ELSE
BEGIN
INSERT INTO ColumnExistenceCheck(COL2)
SELECT 2
END

Thanks!
Raju
http://rajusqlblog.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 08:22:54

This happens simply because table name resolution is done at compile time. So no matter what condition you write to prevent this, column name will always be resolved at compile-time and will give you an error.

The only way to prevent this is to wrap INSERT statement inside EXEC() call.

If col_length('ColumnExistenceCheck','COL3') is not null
BEGIN
EXEC('INSERT INTO ColumnExistenceCheck(COL3)
SELECT COL3 FROM ColumnExistenceCheck')
END

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2008-02-19 : 08:23:53

Interestingly,it works when I use the dynamic SQL.
declare @dsql varchar(4000)
IF EXISTS(SELECT * FROM SYS.COLUMNS
WHERE [name]='COL3' AND OBJECT_ID=OBJECT_ID('ColumnExistenceCheck'))
BEGIN
PRINT'COL3 EXISTS'
set @dsql='INSERT INTO ColumnExistenceCheck(COL3)
SELECT COL3 FROM ColumnExistenceCheck'

END

ELSE
BEGIN
set @dsql='INSERT INTO ColumnExistenceCheck(COL2)
SELECT 2'
END
EXEC(@dsql)

Thanks,
Raju

Thanks!
Raju
http://rajusqlblog.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources