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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply