So I posted a teaser in the puzzles forum. Without running this, try to guess what will happen?
- 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 from a blogpost I made a while back: Do you depend on sp_depends (no pun intended)
First create this proc
- CREATE PROC SomeTestProc
- AS
- SELECT dbo.somefuction(1)
- GO
now create this function
- CREATE FUNCTION somefuction(@id INT)
- RETURNS INT
- AS
- BEGIN
- SELECT @id = 1
- RETURN @id
- END
- Go
now run this
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’
XML Feeds
