column exists issue - 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 exists issue

Author

Topic

ibin
Starting Member

26 Posts

Posted - 2009-10-13 : 06:03:18

hi i am trying to check if a column exists and then select that statement using the code below. but it executes the select statement irrespective of whether it exists or not and gives an error message saying invalid column name

if Exists(select * from information_schema.columns where table_Name = N'Temp' and column_name like N'test%')
begin
Select testcol from Temp
end

let me know if iam missing anything here

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-13 : 06:06:40

if Exists(select * from information_schema.columns where table_Name = N'Temp' and column_name = N'testcol')
begin
Select testcol from Temp
end

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-13 : 06:07:08

why do you check with like operator? The column name can be something that starts with test but not the exact name testcol.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-10-13 : 06:10:06

Do you have another column with test<smthg> as the name?

You could see by doing:


if Exists(select 1 from information_schema.columns where table_Name = N'Temp' and column_name like N'test%') 
begin
select 'got another column with name test something'
end

Another way around this is to use:


if Exists(select 1 from information_schema.columns where table_Name = N'Temp' and column_name like N'test%') 
begin
exec sp_executesql N'Select testcol from Temp'
end

Then it will not precompile the statement.

ibin
Starting Member

26 Posts

Posted - 2009-10-13 : 06:35:51

Thanks Rick...for ur reply.. it works...

but for the statement like below throws incorrect syntax..
if Exists(select * from information_schema.columns where table_Name = N'Temp' and column_name = N'test')
begin
exec sp_executesql N'Select [test] as ID ,'' As test1 from Temp where [test] like 'test''
end

wht is the actual syntax??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-13 : 06:39:44

exec sp_executesql N'Select [test] as ID ,'''' As test1 from Temp where [test] like ''test''%'

Madhivanan

Failing to plan is Planning to fail

ibin
Starting Member

26 Posts

Posted - 2009-10-13 : 06:45:29

Thanks all..... it got resolved...!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-13 : 06:54:59

Also why do you need dynamic sql?

Madhivanan

Failing to plan is Planning to fail

ibin
Starting Member

26 Posts

Posted - 2009-10-13 : 08:15:25

for the above stated requirement.. is there any other means...??do let me know.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-13 : 08:27:40

if Exists(select * from information_schema.columns where table_Name = N'Temp' and column_name = N'test')
begin
Select [test] as ID ,'' As test1 from Temp where [test] like 'test'
end

Madhivanan

Failing to plan is Planning to fail

ibin
Starting Member

26 Posts

Posted - 2009-10-13 : 08:43:56

thanks.. i tried this but it was still compiling the select statement inside and gave an invalid column error.
and just to avoid that i opted for dynamic sql.

Now there is one more issue. actually my query is like this
update table
set col1 = test
from table join
(select col1 as col, col2 as test from tbl where bla bla bla
union
select testcol as col, testcol2 as test from tbl where ---)
ckl on -----continiues..

here there are possibility tht i dont have the columns which i am trying to access so wanted to use the if condition. But now can we use the if exists statement between union..??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources