CODEWITHSUNDEEP
sql-servert-sql
Moe Sisko
Moe Sisko

Reputation: 12005

Invalid column not detected when #temp table is used

Example: 

create table dbo.t1 (id int)
if OBJECT_ID('dbo.s_Test') is not null drop proc dbo.s_Test 
GO
create proc dbo.s_Test 
as
    create table #t2 (id2 int)
    select t.id, t.xyz from dbo.t1 t join #t2 t2 on t2.id2 = t.id
GO

When the proc s_Test is created, I was expecting an error like "Invalid column name 'xyz', But there is no error at proc creation time. It seems the #temp table seems to have something to do with it, as if I create this version with a table variable: 

if OBJECT_ID('dbo.s_Test2') is not null drop proc dbo.s_Test2 
GO
create proc dbo.s_Test2 
as
    declare @t2 table (id2 int)    
    select t.id, t.xyz from dbo.t1 t join @t2 t2 on t2.id2 = t.id
GO

I get error: Invalid column name 'xyz'. Any ideas on why the #temp table version does not throw an error at stored proc creation time? Using SQL Server 2012, in case that matters.

Upvotes: 4

Views: 1206

Answers (1)

osiris_v6
osiris_v6

Reputation: 462

It has to do with Deferred Name Resolution and Compilation. More info here: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190686(v=sql.105)

At compile time, the temp table/variable does not exist, so the statement won't be compiled. It will throw an error at execution time. I know, it sucks.

If you run the statement with table variable first without dbo.t1 beeing create before, it also won't throw an error.

Upvotes: 5

Related Questions