Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Your profile

Tools

Tag cloud

book bug business intelligence database dates dmv functions gemini gotcha how to howto indexing interview madison performance performance tuning postgresql programming sql sql friday sql server sql server 2000 sql server 2005 sql server 2008 sql server 2008 r2 sqlserver t-sql tip trick xml

Authors

Main Categories

Search

XML Feeds

What is RSS?

Google Ads

« FIX for A MERGE statement may not enforce a foreign key constraint when the statement updates a unique key column that is not part of a clustering key bugGet A Free SQL Server 2008 Ebook »
4
comments

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

by SQLDenis on Sep 08, 2008 in categories Data Modelling & Design
Rate Post:
submit to reddit Digg!FacebookDotnetkicks

So I posted a teaser in the puzzles forum. Without running this, try to guess what will happen?

tsql
Line number Off | Hide | Select all
  1. DECLARE @x INT
  2.  
  3. SET @x = 1
  4.  
  5. IF (@x = 0)
  6. BEGIN
  7.     SELECT 1 AS VALUE INTO #temptable
  8. END
  9. ELSE
  10. BEGIN
  11.    SELECT 2 AS VALUE INTO #temptable
  12. END
  13.  
  14. 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

tsql
Line number Off | Hide | Select all
  1. DECLARE @x INT
  2.  
  3. SET @x = 1
  4.  
  5. CREATE TABLE #temptable (VALUE INT)
  6. IF (@x = 0)
  7. BEGIN
  8.     INSERT #temptable
  9.     SELECT 1
  10. END
  11. ELSE
  12. BEGIN
  13.     INSERT #temptable
  14.     SELECT 2
  15. END
  16.  
  17. 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

tsql
Line number Off | Hide | Select all
  1. DECLARE @x INT
  2.  
  3. SET @x = 1
  4.  
  5. IF (@x = 0)
  6. BEGIN
  7.     SELECT 1 AS VALUE INTO temptable
  8. END
  9. ELSE
  10. BEGIN
  11.    SELECT 2 AS VALUE INTO temptable
  12. END
  13.  
  14. SELECT * FROM temptable –what does this return

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

tsql
Line number Off | Hide | Select all
  1. DECLARE @x INT
  2.  
  3. SET @x = 1
  4.  
  5. IF (@x = 0)
  6. BEGIN
  7.     DECLARE @i INT
  8.     SELECT @i = 5
  9. END
  10. ELSE
  11. BEGIN
  12.    DECLARE @i INT
  13.    SELECT @i = 6
  14. END
  15.  
  16. 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

tsql
Line number Off | Hide | Select all
  1. CREATE PROC SomeTestProc
  2. AS
  3. SELECT dbo.somefuction(1)
  4. GO

now create this function

tsql
Line number Off | Hide | Select all
  1. CREATE FUNCTION somefuction(@id INT)
  2. RETURNS INT
  3. AS
  4. BEGIN
  5. SELECT @id = 1
  6. RETURN @id
  7. END
  8. Go

now run this

tsql
Line number Off | Hide | Select all
  1. 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

tsql
Line number Off | Hide | Select all
  1. SELECT specific_name,*
  2. FROM information_schema.routines
  3. WHERE object_definition(OBJECT_ID(specific_name)) LIKE ‘%somefuction%’
  4. AND routine_type = ‘procedure’

About the Author

User bio imageDenis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Social SitingsTwitterFacebookLinkedInHomePageLTD RSS Feed
1609 views
More Posts by SQLDenis
gotcha, howto, sql server, t-sql, tip, trick
submit to reddit Digg!FacebookDotnetkicks

Comments and Feedback

4 comments

Comment from: traingamer [Member] Email
OK, so if you declare @i only once outside of the IF, that will fix the second example, but what about the temptable? Do you have to specifically create a temptable before the SELECT .. INTO... to make it work?
09/08/08 @ 08:35
Comment from: SQLDenis [Member] Email
traingamer , you can only create the temp table once in the batch of code

Here is another thing for you to ponder :-)

DECLARE @x INT

SET @x = 0

CREATE TABLE #temptable (VALUE INT identity)
IF (@x = 0)
BEGIN
ALTER TABLE #temptable ADD Col2 varchar(40) default 'bla'
INSERT #temptable DEFAULT VALUES

END
ELSE
BEGIN
ALTER TABLE #temptable ADD Col2 int default 3
INSERT #temptable DEFAULT VALUES
END


SELECT * FROM #temptable

DROP TABLE #temptable
09/08/08 @ 08:49
Comment from: Emtucifor [Member] Email
Or if you want, the poor man's create table is SELECT INTO WHERE 1 = 0

You just have to specify some columns that are the correct data types.

Now you can INSERT to your heart's content.
09/08/08 @ 11:44
Comment from: traingamer [Member] Email
I got here from your forum post and I completely missed your CREATE table #temptable example in your second block. My last comment is silly as a result (especially since SELECT...INTO always creates a table). Sorry about that.

09/09/08 @ 11:08

Leave a comment


Your email address will not be revealed on this site.

Your URL will be displayed.
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)

Powered by B2Evolution
Valid XHTML | Valid CSS