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.