Object binding

  • Toreador (8/15/2013)

    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


    declare @i int

    set @i=1

    if (@i<>1)


    select c from dbo.non_existing;




    select c from dbo.existing;



    --Third batch

    execute dbo.test_existing;


    Toreador, you have indeed identified a mistake I made. I deleted the rest of this post because I was just compounding that error.

    Jason Wolfkill

  • Stewart "Arturius" Campbell (8/15/2013)

    Good back-to-stored proc basics question.

    +1 🙂

    Vinay Kumar
    Keep Learning - Keep Growing !!!

  • Very good question....

    There is a lil confusion to me....

    When the second batch gets executed the Stored procedure created successfuly, as according to deferred name resolution process when an entry is made in a sys.sql_modules in which an object is used in a SP that is not existed, SP will get create successfully...

    Now, while 3 batch gets executed, it does not the throw error and gets executed successfully as condition (1<>1) never gets executed. So, Does at the time of compilation only valid condition gets compiled and rest remained untouched?

    While executing 4 batch, I read somewhere that after getting an entry into a sys.sql_module if we create an non existing object in a stored procedure it will give you an error as now it will check that all objects using in a SP exists or not....

    To get quick answer follow this link:

  • Good question, but do agree the explanation could be improved.

  • Great question. 2) and 4) should give a warning about the nonexistent table, but will run nevertheless.

    I don't understand why 5% of the answers don't include the first option. I mean why would the create table statement not succeed :blink:?

  • Tough time!!!!! 😉

  • Easy question for me. Experience paid for me. 🙂

Viewing 7 posts - 16 through 21 (of 21 total)

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