on May 21, 2018
I got a question recently about why a particular bit of code was failing when running.
My first thought: oh, that’s probably related to… whatsitcalled.
You know, whatsitcalled, one of those features you notice just often enough so that it’s name doesn’t come to mind immediately.
It’s deferred.
In this case, it was actually named “Deferred Name Resolution.”
Deferred name resolution lets you create a procedure referencing something that doesn’t exist
In this case, I’m creating a temporary stored procedure (out of laziness, it means I don’t have to clean up a quick demo) -
CREATE OR ALTER PROCEDURE #test
AS
IF 1=0
EXECUTE dbdoesnotexist.dbo.someproc;
GO
The database dbdoesnotexist does NOT exist, but I’m still allowed to create the procedure.
When I do so, I get an informational message:
The module ‘#test’ depends on the missing object ‘dbdoesnotexist.dbo.someproc’. The module will still be created; however, it cannot run successfully until the object exists.
This can be useful in some cases where you’ll be querying a table or procedure that may not exist all the time, but which will exist when a certain code block is run.
You can’t always count on deferred name resolution
What if our code refers to something that may exist, but isn’t accessible?
Here’s a slightly different code sample:
CREATE DATABASE offlinedb;
GO
ALTER DATABASE offlinedb SET OFFLINE;
GO
CREATE OR ALTER PROCEDURE #test
AS
IF 1=0
execute offlinedb.dbo.someproc;
GO
This results in error 942
Creating the procedure fails in this case. The error given is:
Msg 942, Level 14, State 4, Procedure #test, Line 5 [Batch Start Line 17] Database ‘offlinedb’ cannot be opened because it is offline.
If I set the empty database “offlinedb” to be online, then deferred name resolution works and I can create #test. If I drop “offlinedb”, same thing– no problems.
But while offlinedb exists in an offline state, I get error 942 at the time I attempt to create the procedure.
Takeaways
Little quirks like this are a good argument to configure test and pre-production/staging environments in ways that mirror production as much as possible.
And if you think you might run into this situation, it might be worth using a bit of Dynamic SQL to avoid it!
REGISTER