Temp Table Naming In Stored Procedures

Just found a quirk in T-SQL that i did not know existed before.

You cannot use the same Temp Table name twice in the same stored procedure, even if the declaration of the temp table is in two separate logical blocks of code.

For example;

create proc dbo.Test
@inParam int
as

If @inParam <= 0
begin
Create table #Foo(
id int
)
end
else
begin
Create table #Foo(
dx int
)
end
go

The above will not compile successfully in SMS!!! So watch out for this and adjust your coding style.

You must use a different name for the second declaration, like this;

create proc dbo.Test
@inParam int
as

If @inParam <= 0
begin
Create table #Foo(
id int
)
end
else
begin
Create table #Foo2(
dx int
)
end
go


Notice the second temp table is now called Foo2.

I know this is an unimportant disclosure but maybe bringing this to the forefront someone can save a little time.