Post reply

Evaluated drop table and ignore current context

  • July 20, 2015 at 3:56 am

    #319938

    I have 2 stored procedure, insert and update.

    They both drop and use a static table, this will be changed for production to use a temp table. there is a slight difference in how the create the table and when it comes to executing them if the other has been run first it errors because of the column definition or names.

    Now as I said I will change these to use a temp table rather than a static staging table for production but as one of the first parts of my proc uses the IF OBJECT_ID ('dbo.tab', 'U') IS NOT NULL DROP TABLE dbo.Tab;

    Then I would expect the table difference to not matter as the table is being dropped

    Anyone know why this isn't the case?

  • July 20, 2015 at 4:02 am

    #1813607

    Because SQL's execution model is parse, bind then execute. The entire module (procedure) is parsed and checked for validity before any part of it starts executing. The incorrect column errors come from the validity checks, well before anything, including the drop/create has executed. Hence the checks are against whatever table exists at the time the procedure starts.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 20, 2015 at 4:11 am

    #1813609

    Thanks 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

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