Post reply

T-SQL behavior

  • May 8, 2008 at 11:05 am

    #75208

    Hi:

    I have a confusion using the IF EXISTS statement and here's how it goes:

    case#1

    if exists(select * from syscolumns where id = object_id('orders') and name = 'cproperty')

    select cproperty from orders

    and I got this:

    Server: Msg 207, Level 16, State 3, Line 1

    Invalid column name 'cproperty'.

    I know that cproperty column doesn't exists in table orders but I wonder why the IF statement didn't validate/capture in the 1st place? :doze:

    However if I pass it with a variable / using execute statement then it won't give me any error. Just like below:

    case#2

    declare @xcmd varchar(500)

    if exists(select * from syscolumns where id = object_id('orders') and name = 'cproperty')

    begin

    set @xcmd = 'select cproperty from orders'

    execute(@xcmd)

    end

    or

    case#3

    if exists(select * from syscolumns where id = object_id('orders') and name = 'cproperty')

    execute('select cproperty from orders')

    So, basically what my question is how does t-sql behaves in running the script? Does it check 1st the existence of tables/fields before running the whole process of the script?

    Hoping for your kind feedbacks.

    Thanks,

    Jan

  • May 8, 2008 at 12:44 pm

    #812622

    SQL is trying to compile your entire batch before it can be run and cannot resolve the name cproperty. When you use EXEC(string), that has it's own batch that is not created(then compiled, then executed) until you actually execute the EXEC command.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • May 8, 2008 at 1:16 pm

    #812644

    SQL compiler doesn't return any error message, if the table doesn't exist. Check this,

    [font="Courier New"]if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SomeThing]')

    and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    select test from SomeThing

    else

    select * from Orders[/font]

    - Zahran -

  • May 8, 2008 at 1:25 pm

    #812648

    Thanks you guys... I really appreciate it. 🙂

  • May 8, 2008 at 1:58 pm

    #812674

    glad we could help.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply