Stored procedure able to be built over table that doesn't exist

  • SQL 2016 SP3

    So we're updating some procedures to use a different table, it's the same name but different schema, neither one uses the dbo schema.

    The table under the old schema has been deleted.

    I found that I was able to successfully build the procedure even though I missed changing one of the references to the new schema. It does fail to execute.

    The assumption is if it builds that all references were updated. Now it seems I need to go back and double check.

    Is there a way to build a procedure, like a keyword or something to make sure it actually verifies the objects exist?

     

     

  • If you prefer, just point the old name to the new table; you can, and definitely should, still continue to clean up the old name after that, but it should prevent failures.

    CREATE SYNONYM table_schema.old_table_name FOR other_table_schema.new_table_name;

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • The behavior is called "late binding" and for many workflows and from many points of view it's a feature and not a flaw.  Early binding is available in SQL Server functions by specifying WITH SCHEMABINDING.  In stored procedures the proc must be natively compiled to enable WITH NATIVE_COMPILATION, SCHEMABINDING afaik.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • That's a great idea, thank you for the suggestion.

    It would solve the execution failure but still wondering why a procedure would build with an invalid table reference.

  • Steve thanks for the info! I'm surprised I've not run across this sooner. In fact just today I was compiling these and getting errors, but then on another one I wasn't seemed very odd although the one I did notice the reference was in a conditional branch and that may have been why.

    Glad to learn something new today, thanks!

  • I just created a database containing the following:

    • schemas tst and tst2
    • table tst.Test1
    • proc tst.SomeProc, which selects from tst2.Test1

    Clearly, tst2.Test1 does not exist and this results (by default) in a build warning (not an error) "Unresolved reference ..."

    To turn this into a build error, I went into Project/Properties and set 'Treat Transact-SQL warnings as errors'. Use this, along with the 'Suppress' option below it, to achieve your aim.

    Build

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 6 posts - 1 through 6 (of 6 total)

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