Reunindo um procedimento rápido para ajudar na depuração, encontrei o que parece ser um erro no compilador.

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;

A tentativa acima retorna o seguinte erro

Msg 2714, Nível 16, Estado 1, Procedimento spFoo, Linha 19
Já existe um objeto denominado '#bar' no banco de dados.

Em um sentido legível por humanos, o proc parece estar bem: apenas uma select intoinstrução será executada, uma vez que está dentro dos if-elseblocos. Muito bem, porém, o servidor SQL não pode confirmar que as instruções são logicamente excluídas umas das outras. Talvez mais confuso seja que o erro permanece quando o drop table #fooé colocado dentro do bloco if-else (que se supõe que diria ao compilador para desalocar o nome do objeto) como abaixo.

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;

O proc em si é bom. Eu absorvi e escrevi as instruções create table #foo( ... )e insert #foo ( ... ), eu estava tentando pular com a select * into sintaxe. Neste ponto, estou apenas tentando entender por que o compilador me cagou com a sintaxe do cara preguiçoso. A única coisa que consigo pensar é que o comando DDL reserva o nome do objeto IN TEMPDB .

Por que o texto em negrito?

create proc spIck
as
begin
    create table #ack ( col1 int );
    drop table #ack;
    create table #ack ( colA char( 1 ) );
    drop table #ack;
end;

Isso falha com o mesmo código de erro acima. Mas o seguinte...

create proc spIck
as
begin
    create table ack ( col1 int );
    drop table ack;
    create table ack ( colA char( 1 ) );
    drop table ack;
end;

... consegue. O mesmo segue acima para a tentativa de proc original. Então...

Minha pergunta é esta

Qual é a diferença (e por que está presente) na reserva de nome de objeto para TempDBobjetos em oposição aos bancos de dados do usuário. Nenhuma das referências de processamento de consulta lógica nem as referências de comando DDL que revisei parecem explicar isso.