April 15, 2016 at 1:19 pm
I am stumped on this one. I am scanning databases to determine a piece of info if a certain column exists in a table in each database. If the column doesn't exist, then I don't want to scan the database. So.. the basic query, testing against a single database:
IF EXISTS (select name from syscolumns where name = 'family')
BEGIN
select 'Yes exists'
END
ELSE begin
select 'Does not exist'
END
This works. Now if I add my actual query... it errors that the column does not exist and doesn't run the ELSE statement.
IF EXISTS (select name from syscolumns where name = 'family')
BEGIN
select 'Yes exists'
select DB_Name(), count(distinct family) as Total
from mytable with (nolock)
where family > 0
END
ELSE begin
select 'Does not exist'
END
Msg 207, Level 16, State 1, Line 5
Invalid column name 'family'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'family'.
Why is it running the IF and not the ELSE? I do IF statements routinely but usually on a field level. This is really bugging me! I tried a bunch of different ways other than throwing a lot of code at it. It shouldn't be that difficult.
April 15, 2016 at 1:23 pm
the If does not shortcut the validation for objects and columns. all objects must exist before the statement executes. you see the smae behavior if you have an ADD column , and reference the column in the next line.
you can use dynamic sql instead;
IF EXISTS (select name from syscolumns where name = 'family')
BEGIN
select 'Yes exists'
EXEC('select DB_Name(), count(distinct family) as Total
from mytable with (nolock)
where family > 0 ')
END
ELSE begin
select 'Does not exist'
END
Lowell
April 15, 2016 at 1:35 pm
Aaah. Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply