IF EXISTS still running subquery when column not found

• Mindy Hreczuck

  SSCommitted

  Points: 1796

  More actions

  April 15, 2016 at 1:19 pm

  #149273

  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.

• Lowell

  SSC Guru

  Points: 323522

  More actions

  April 15, 2016 at 1:23 pm

  #1872500

  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

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Mindy Hreczuck

  SSCommitted

  Points: 1796

  More actions

  April 15, 2016 at 1:35 pm

  #1872502

  Aaah. Thank you!