Deferred Name Resolution Usage

When deferred name resolution is active objects inside procedures are resolved at execution time, instead of at creation time.

You can use this option to create procedures that reference objects that did not exist when the procedure was created. For example, using deferred_name_resolution allows creating a procedure that references a table that does not yet exist. This example shows an attempt to create a procedure without deferred_name_resolution:

select * from non_existing_table
error message
Msg 208, Level 16, State 1:
Line 1:
non_existing_table not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

However, using this option allows you to create the procedure without raising an error because of the missing objects.

set deferred_resolution_on
create proc p as select * from non_existing_table
Note: deferred_name_resolution does not resolve user-defined datatypes at execution. They are resolved at creation time, so if the resolution fails, the procedure cannot be created.

Resolving objects at creation time means that object resolution errors are also raised at execution, not creation.