What is deferred name resolution and why do you need to care?

DECLARE @x INT

SET @x = 1

IF (@x = 0)
BEGIN
SELECT 1 AS VALUE INTO #temptable
END
ELSE
BEGIN
SELECT 2 AS VALUE INTO #temptable
END

SELECT * FROM #temptable –what does this return

This is the error you get
Server: Msg 2714, Level 16, State 1, Line 12
There is already an object named ‘#temptable’ in the database.

You can do something like this to get around the issue with the temp table

DECLARE @x INT

SET @x = 1

CREATE TABLE #temptable (VALUE INT)
IF (@x = 0)
BEGIN
INSERT #temptable
SELECT 1
END
ELSE
BEGIN
INSERT #temptable
SELECT 2
END

SELECT * FROM #temptable –what does this return


So what is thing called Deferred Name Resolution? Here is what is explained in Books On Line

When a stored procedure is created, the statements in the procedure are parsed for syntactical accuracy. If a syntactical error is encountered in the procedure definition, an error is returned and the stored procedure is not created. If the statements are syntactically correct, the text of the stored procedure is stored in the syscomments system table.

When a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the syscomments system table of the procedure and checks that the names of the objects used by the procedure are present. This process is called deferred name resolution because objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed.

In the resolution stage, Microsoft SQL Server 2000 also performs other validation activities (for example, checking the compatibility of a column data type with variables). If the objects referenced by the stored procedure are missing when the stored procedure is executed, the stored procedure stops executing when it gets to the statement that references the missing object. In this case, or if other errors are found in the resolution stage, an error is returned.

So what is happening is that beginning with SQL server 7 deferred name resolution was enabled for real tables but not for temporary tables. If you change the code to use a real table instead of a temporary table you won’t have any problem
Run this to see what I mean

DECLARE @x INT

SET @x = 1

IF (@x = 0)
BEGIN
SELECT 1 AS VALUE INTO temptable
END
ELSE
BEGIN
SELECT 2 AS VALUE INTO temptable
END

SELECT * FROM temptable –what does this return

What about variables? Let’s try it out, run this


DECLARE @x INT

SET @x = 1

IF (@x = 0)
BEGIN
DECLARE @i INT
SELECT @i = 5
END
ELSE
BEGIN
DECLARE @i INT
SELECT @i = 6
END

SELECT @i

And you get the follwing error
Server: Msg 134, Level 15, State 1, Line 13
The variable name ‘@i’ has already been declared. Variable names must be unique within a query batch or stored procedure.

Now why do you need to care about deferred name resolution? Let’s take another example
create this proc

CREATE PROC SomeTestProc
AS
SELECT dbo.somefuction(1)
GO

CREATE FUNCTION somefuction(@id INT)
RETURNS INT
AS
BEGIN
SELECT @id = 1
RETURN @id
END
Go

now run this

SP_DEPENDS ’somefuction’

result: Object does not reference any object, and no objects reference it.

Most people will not create a proc before they have created the function. So when does this behavior rear its ugly head? When you script out all the objects in a database, if the function or any objects referenced by an object are created after the object that references them then sp_depends won’t be 100% correct

SQL Server 2005 makes it pretty easy to do it yourself

SELECT specific_name,*
FROM information_schema.routines
WHERE object_definition(OBJECT_ID(specific_name)) LIKE ‘%somefuction%’
AND routine_type = ‘procedure’