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

Question

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

TangoVictor

SSChampion

Points: 10975
More actions
August 5, 2022 at 5:33 pm

#4073098

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?

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

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

ScottPletcher SSC Guru Points: 101130 More actions · Answer 1

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".

Steve Collins SSCrazy Points: 2176 More actions · Answer 2

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

TangoVictor SSChampion Points: 10975 More actions · Answer 3

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.

TangoVictor SSChampion Points: 10975 More actions · Answer 4

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!

Phil Parkin SSC Guru Points: 246991 More actions · Answer 5

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel