Putting together a quickie proc to help with debugging, I ran into a what seems to be a error in the compiler.
create proc spFoo
@param bit
as
begin
if @param = 0
begin
select *
into #bar
from [master].dbo.spt_values
-- where number between ...
end
else
begin
select top 10 *
into #bar
from [master].dbo.spt_values
order by newid();
end;
end;
Attempting the above returns the following error
Msg 2714, Level 16, State 1, Procedure spFoo, Line 19
There is already an object named '#bar' in the database.
In a human-readable sense, the proc appears to be fine: only one select into
statement will ever be executed since they're wrapped inside the if-else
blocks. Very well though, SQL server can't confirm that the statements are logically excluded from each other. Perhaps more confusing though is that the error remains when the drop table #foo
is placed inside the if-else block ( which one assumes would tell the compiler to deallocate the object name ) as below.
create proc spFoo
@param bit
as
begin
select top 1 *
into #bar
from [master].dbo.spt_values
if @param = 0
begin
drop table #bar;
select *
into #bar
from [master].dbo.spt_values
-- where number between ...
end
else
begin
drop table #bar;
select top 10 *
into #bar
from [master].dbo.spt_values
order by newid();
end;
end;
The proc itself is fine. I sucked it up and wrote the create table #foo( ... )
and insert #foo ( ... )
statements, I'd been trying to skip with the select * into
syntax. At this point, I'm just trying to understand why the compiler crapped out on me with the lazy-guy syntax. The only thing I can think of is that the DDL command reserves the object name IN TEMPDB.
Why the bold text?
create proc spIck
as
begin
create table #ack ( col1 int );
drop table #ack;
create table #ack ( colA char( 1 ) );
drop table #ack;
end;
This fails with the same error code as above. But the following...
create proc spIck
as
begin
create table ack ( col1 int );
drop table ack;
create table ack ( colA char( 1 ) );
drop table ack;
end;
... succeeds. The same follows above to the original proc attempt. So...
My Question Is This
What is the difference ( and why is it present ) in object name reservation for TempDB
objects as opposed to user databases. None of the Logical Query Processing references nor DDL command references I've reviewed appear to explain this.