Deferred Name Resolution

In SQL Server 2000, the object names that a stored procedure references do not have to exist at the time the procedure is created. Versions of SQL Server prior to 7.0 would return an error message and fail to create if a procedure referenced an object, other than another stored procedure, that didn't exist at the time the stored procedure was created. Stored procedures in SQL Server 7.0 and 2000 check for the existence of database objects at the time the stored procedure is executed and return an error message at runtime if the referenced object doesn't exist. The only exception is when a stored procedure references another stored procedure that doesn't exist. In that case, a warning message will be issued, but the stored procedure will still be created (see Listing 28.3).

Listing 28.3 Procedure Name Resolution During Stored Procedure Creation

create proc p2
as
exec p3
go

Cannot add rows to sysdepends for the current stored procedure because it depends on
the missing object 'p3'. The stored procedure will still be created.

In SQL Server 2000, when an object does exist at procedure creation time, the column names in the referenced table will be validated. If a column is mistyped or doesn't exist, the procedure will not be created (see Listing 28.4).

Listing 28.4 Column Name Validation in Stored Procedures

create proc get_authors_and_titles
as

select a.au_lname, au_fname, title, isbn_number
   from authors a join titleauthor ta on a.au_id = ta.au_id
   join titles t on t.title_id = ta.title_id
return
go

Server: Msg 207, Level 16, State 3, Procedure get_authors_and_titles, Line 4
Invalid column name 'isbn_number'.

One advantage of delayed (or deferred) name resolution is the increased flexibility when creating stored procedures; the order of creating procedures and the tables they reference does not need to be exact. It is an especially useful feature when a stored procedure references a temporary table that isn't created within that stored procedure. However, at other times, it can be frustrating for a stored procedure to create successfully only to have it fail when it runs due to a missing table, as shown in Listing 28.5.

Listing 28.5 Runtime Failure of a Stored Procedure with Invalid Object Reference

create proc get_authors_and_titles
as

select a.au_lname, au_fname, title, pub_date
   from authors a join titleauthor ta on a.au_id = ta.au_id
   join books t on t.title_id = ta.title_id

go

exec get_authors_and_titles
go

Server: Msg 208, Level 16, State 1, Procedure get_authors_and_titles, Line 4
Invalid object name 'books'.

Another issue to be careful of with deferred name resolution is that you can no longer rename objects referenced by stored procedures and have the stored procedure continue to work. In versions of SQL Server prior to 7.0, after the stored procedure was created, object references within the stored procedure were made via the object ID rather than the object name. This allowed stored procedures to continue to function properly if a referenced object were renamed. However, now that object names are resolved at execution time, the procedure will fail at the statement referencing the renamed object. For the stored procedure to execute successfully, it needs to be altered to specify the new object name.

Identifying Objects Referenced in Stored Procedures

Because changing the name of a table can cause stored procedures to no longer work, you might want to identify which stored procedures reference a specific table so you'll know which stored procedures will be affected. SQL Server keeps track of the dependencies between database objects in the sysdepends system catalog table. All you'll see if you query the sysdepends table is a bunch of numbers; sysdepends stores just the IDs of the objects that have a dependency relationship along with some additional status information.

The better way to display a list of stored procedures that reference a specific table or view, or to display a list of objects referenced by a stored procedure, is to use the sp_depends system procedure:

exec sp_depends {table_name | procedure_name} 

To display the stored procedures and triggers that reference the titles table, execute the following:

exec sp_depends titles 
go

In the current database, the specified object is referenced by the following:
name                                                           type
-------------------------------------------------------------------------------
dbo.encr_proc                                                  stored procedure
dbo.group_proc                                                 stored procedure
dbo.reptq1                                                     stored procedure
dbo.reptq2                                                     stored procedure
dbo.reptq3                                                     stored procedure
dbo.title_authors                                              stored procedure
dbo.titles_for_an_author                                       stored procedure
dbo.titleview                                                  view

To display the objects referenced by the title_authors stored procedure, execute the following:

exec sp_depends title_authors 
go

In the current database, the specified object references the following:
name                    type          updated selected column
----------------------- ------------- ------- -------- ----------------
dbo.titles              user table    no      no       title
dbo.authors             user table    no      no       au_lname
dbo.authors             user table    no      no       au_fname
dbo.titles              user table    no      no       title_id
dbo.titleauthor         user table    no      no       au_id
dbo.titleauthor         user table    no      no       title_id
dbo.authors             user table    no      no       au_id

Dependency information can also be displayed in Query Analyzer by clicking on the Dependencies folder for an object, or in Enterprise Manager by right-clicking on an object and choosing the Display Dependencies option in the All Tasks submenu.

select distinct object_name(id) 
    from syscomments
    where text like '%tablename%'