Object binding

  • Evgeny Garaev

    SSCertifiable

    Points: 6760

    Comments posted to this topic are about the item Object binding

  • palotaiarpad

    SSCertifiable

    Points: 5615

    Maybe a clear definition of succesful execution would be helpful. In my opinion the statement execute runs fine if the procedure exists. The procedure throws an error not the execute statement.

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    Good question,

    Just a quick BOL reference to clarify things

    A procedure can reference tables that do not yet exist. At creation time, only syntax checking is performed. The procedure is not compiled until it is executed for the first time. Only during compilation are all objects referenced in the procedure resolved. Therefore, a syntactically correct procedure that references tables that do not exist can be created successfully; however, the procedure will fail at execution time if the referenced tables do not exist.

  • Toreador

    SSChampion

    Points: 11261

    Good question, though even if you didn't know the answer the "choose 4" hints should give it away - I guess the 50% (!) getting it wrong didn't notice this.

    The explanation is a bit suspect though.

  • This was removed by the editor as SPAM

  • John Mitchell-245523

    SSC Guru

    Points: 148771

    Interesting. The explanation ought to cite deferred name resolution as the reason it's possible to create stored procedures referencing non-existent tables. The paragraph from BOL quoted is not entirely accurate since not all objects are resolved - only those that are touched in the flow logic.

    John

  • Thomas Abraham

    SSChampion

    Points: 10761

    Thanks for the question. Relearned something I had forgotten, as I do many things at work besides programming - and my brain is getting older! 😉

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • mtassin

    SSC-Insane

    Points: 23099

    Thanks for the question... glad I managed to get through that one without morning caffeine. 🙂



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Dana Medley

    SSCertifiable

    Points: 6764

    Thomas Abraham (8/15/2013)


    Thanks for the question. Relearned something I had forgotten, as I do many things at work besides programming - and my brain is getting older! 😉

    + 1



    Everything is awesome!

  • logitestus

    SSCrazy

    Points: 2878

    I was one of the people who got it wrong... <facepalm/>

    Even without reading the explanation, I realized my error... maybe I should drink my first cup of coffee in the morning before I attempt to engage brain.

    Thanks for the good Q!

  • Jason Wolfkill

    SSCrazy Eights

    Points: 9772

    John Mitchell-245523 (8/15/2013)


    Interesting. The explanation ought to cite deferred name resolution as the reason it's possible to create stored procedures referencing non-existent tables. The paragraph from BOL quoted is not entirely accurate since not all objects are resolved - only those that are touched in the flow logic.

    John

    EDIT: The following seems to be incorrect - see Toreador's post below.

    SQL Server will attempt to resolve all objects except those that it can determine will NEVER be necessary to return results when it parses the query. Otherwise it will attempt to resolve and bind all object references in all logical branches of a query because it can't know which branch will actually execute at runtime. I just wanted to clarify that because we occasionally see questions along the lines of "My stored procedure has an IF statement where one branch has a table that may not exist - why does it throw a non-existent table error when I run it with parameter values that should direct it to the other branch?"

    Here, SQL Server recognizes that "1<>1" will NEVER evaluate to TRUE, so it doesn't bother to parse and bind the IF branch that depends on that condition. If you change the stored procedure to use some condition for which SQL Server can't conclusively rule out a TRUE result (such as "1<>@int" - at runtime, the value of int may or may not be 1), the attempt to execute it will fail.

    Jason Wolfkill

  • Primo Dang

    SSCrazy

    Points: 2643

    Toreador (8/15/2013)


    Good question, though even if you didn't know the answer the "choose 4" hints should give it away - I guess the 50% (!) getting it wrong didn't notice this.

    The explanation is a bit suspect though.

    I thought the 4th batch would throw an error because of the non-existing table in a "reachable" point of the flow, and then the 5th batch would execute successfully the original version of the procedure.

  • Toreador

    SSChampion

    Points: 11261

    wolfkillj (8/15/2013)


    If you change the stored procedure to use some condition for which SQL Server can't conclusively rule out a TRUE result (such as "1<>@int" - at runtime, the value of int may or may not be 1), the attempt to execute it will fail.

    I assume you didn't try this, as it's not true.

    The procedure will only fail at runtime if that particular select is reached. If the logic means that it is not reached then it will not fail, regardless of whether it is reachable or not.

    So the following with both compile and execute

    create procedure dbo.test_existing

    as

    declare @i int

    set @i=1

    if (@i<>1)

    begin

    select c from dbo.non_existing;

    end

    else

    begin

    select c from dbo.existing;

    end

    go

    --Third batch

    execute dbo.test_existing;

    go

  • John Mitchell-245523

    SSC Guru

    Points: 148771

    SQL Server will attempt to resolve all objects except those that it can determine will NEVER be necessary to return results when it parses the query. Otherwise it will attempt to resolve and bind all object references in all logical branches of a query because it can't know which branch will actually execute at runtime. I just wanted to clarify that because we occasionally see questions along the lines of "My stored procedure has an IF statement where one branch has a table that may not exist - why does it throw a non-existent table error when I run it with parameter values that should direct it to the other branch?"

    Here, SQL Server recognizes that "1<>1" will NEVER evaluate to TRUE, so it doesn't bother to parse and bind the IF branch that depends on that condition. If you change the stored procedure to use some condition for which SQL Server can't conclusively rule out a TRUE result (such as "1<>@int" - at runtime, the value of int may or may not be 1), the attempt to execute it will fail.

    Good explanation - thanks! Have you considered becoming an author for BOL?

    I assume you didn't try this, as it's not true.

    The procedure will only fail at runtime if that particular select is reached. If the logic means that it is not reached then it will not fail, regardless of whether it is reachable or not.

    So the following with both compile and execute

    But that's the whole point. The non-existent table isn't reachable, and so it doesn't fail.

    John

  • Toreador

    SSChampion

    Points: 11261

    John Mitchell-245523 (8/15/2013)But that's the whole point. The non-existent table isn't reachable, and so it doesn't fail.

    No. It's not reached, so it doesn't fail. It can be reachable and will work so long as it doesn't actually get there.

    create procedure dbo.test_existing(@i int)

    as

    if (@i<>1)

    begin

    select c from dbo.non_existing;

    end

    else

    begin

    select c from dbo.existing;

    end

    go

    --Third batch

    execute dbo.test_existing 1 ;

    go

    or

    create procedure dbo.test_existing

    as

    declare @i int

    set @i=case when RAND() < .5 then 1 else 0 end

    if (@i<>1)

    begin

    select c from dbo.non_existing;

    end

    else

    begin

    select c from dbo.existing;

    end

    go

    --Third batch

    execute dbo.test_existing ;

    go

    the procedure will compile OK. When you execute it, it will sometimes give an error, sometimes not.

Viewing 15 posts - 1 through 15 (of 22 total)

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