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.

NOTE

Unfortunately, the dependency information is built only when a stored procedure is created. If a table is dropped and re-created with the same name, the stored procedure will continue to work, but the dependency information will be deleted when the table is dropped. Another way to identify any stored procedures or other objects that reference a table is to search the text of the stored procedure with a query similar to the following. (Replace tablename with the name of the object or other text for which you want to search.)

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

This method is not foolproof either. If a stored procedure is larger than 4000 bytes, the tablename could be split across rows in the syscomments table and wouldn't match the search argument. The syscomments table is covered in more detail in the next section.