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.