Stored Proc compiles even when there is no CREATE TABLE for a temp table

Question

Stored Proc compiles even when there is no CREATE TABLE for a temp table

Vinoo

Say Hey Kid

Points: 670
More actions
February 11, 2015 at 2:56 pm

#301020

Hi All,
I think that the below snippet should give a compilation error because I am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END
--only on calling the proc does this give an execution error
EXEC testTemp

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Nevyn SSChampion Points: 14210 More actions · Answer 1

A language correction that may help you understand why you aren't getting an error.

When you execute the CREATE, you are NOT compiling it. It compiles the first time you try to execute it. When you create it will check T-SQL syntax, but not try to form a query plan, or even see if it can run any of the queries.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

This is called deferred name resolution. Here is some MS documentation on this behavior.

https://technet.microsoft.com/en-us/library/ms190686%28v=sql.105%29.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 3

Keep in mind, that if you do this, or potentially do this, then the dependecies you can query in SQL Server will not be correct. Deferred Name Resolution breaks sys.dependencies.