Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Posts
    9

    Unanswered: Checking for a column's existence

    I need to check if a particular column exists in a table. If it does then I need to use it in a calculation, otherwise I don't. Here is the sql statement I currently have. It complains when I try to use column I01 in the select statement, even though the if condition evaluates to false. Any suggestions?

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns
    WHERE (TABLE_NAME = 'Test') AND column_name = 'I01')
    SELECT 0
    ELSE
    SELECT I01 FROM Test

  2. #2
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78

    Re: Checking for a column's existence

    Can you work it out by looking for the column name in the syscolumns database?

  3. #3
    Join Date
    Sep 2002
    Posts
    9

    Re: Checking for a column's existence

    Originally posted by andyabel
    Can you work it out by looking for the column name in the syscolumns database?
    It's the same problem. I can find whether it exists or not, but how do I use it in the select statement if it does? Apparently, it checks for the syntax of the query, before evaluating the IF condition.

  4. #4
    Join Date
    Jul 2002
    Posts
    229
    Maybe there's a setting that tells whether to verify all code clauses before running, or to verify only at run-time?
    (Can't find it in BOL, though, but maybe someone else can)

  5. #5
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    Can you fool the compiler by using exec()? e.g.:

    declare @string varchar(100)
    set @string='SELECT I01 FROM Names'
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns
    WHERE (TABLE_NAME = 'Test') AND column_name = 'I01')
    SELECT 0
    ELSE
    exec (@string)

  6. #6
    Join Date
    Sep 2002
    Posts
    9
    Originally posted by andyabel
    Can you fool the compiler by using exec()? e.g.:

    declare @string varchar(100)
    set @string='SELECT I01 FROM Names'
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns
    WHERE (TABLE_NAME = 'Test') AND column_name = 'I01')
    SELECT 0
    ELSE
    exec (@string)

    Thanks for all of your help guys, I think I got it to work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •