Escopo de tabelas temporárias com SQL dinâmico

Uma procedure cria uma tabela temporária usando SQL dinâmico e tenta consultá-la depois. A inserção funciona, mas a leitura informa que o objeto não existe. A causa habitual é escopo: uma tabela externa pode ser visível no trabalho aninhado, enquanto uma criada dentro não sobrevive ao fim daquele contexto.

Criar no contexto que controla a duração

O exemplo cria #OuterWork no lote chamador. O SQL dinâmico insere nela e cria sua própria #InnerWork. Depois que termina, #OuterWork continua disponível. Outra consulta dinâmica contra #InnerWork produz o erro 208, capturado por TRY/CATCH.

CREATE TABLE #OuterWork (ItemId int NOT NULL PRIMARY KEY);

EXEC sys.sp_executesql N'
    INSERT #OuterWork (ItemId) VALUES (@Id);
    CREATE TABLE #InnerWork (ItemId int NOT NULL);
    INSERT #InnerWork VALUES (99);
    SELECT ItemId AS VisibleInside FROM #InnerWork;',
    N'@Id int', @Id = 7;

SELECT ItemId AS VisibleOutside FROM #OuterWork;

BEGIN TRY
    EXEC sys.sp_executesql N'SELECT ItemId FROM #InnerWork;';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

DROP TABLE #OuterWork;

O primeiro resultado contém 99, o segundo 7 e o último descreve o objeto ausente. Não existe uma segunda conexão. A fronteira está dentro da mesma sessão; manter a conexão aberta não preserva a tabela interna.

Quando instruções posteriores precisam das linhas, crie a tabela com esquema explícito no escopo externo e deixe o lote dinâmico preenchê-la. O contrato fica visível: o chamador define a estrutura, o trabalho aninhado produz as linhas e o chamador consome e remove o objeto.

O raciocínio também vale para procedures. Uma tabela temporária local criada por uma procedure desaparece ao término dela, embora procedures aninhadas possam usá-la durante sua existência. Uma procedure pode acessar uma tabela criada pelo chamador, mas isso cria uma dependência implícita. Documente colunas e constraints esperadas.

Separar valores, esquemas e conexões

sp_executesql executa um lote separado. Variáveis escalares comuns do chamador não ficam disponíveis automaticamente. Passe valores como parâmetros tipados, como @Id no exemplo. A visibilidade de uma tabela temporária existente segue uma regra diferente. Confundir as duas costuma levar à concatenação desnecessária de valores.

Uma variável de tabela também não fica acessível ao SQL dinâmico apenas porque sua declaração está próxima. Para entrada de várias linhas, um parâmetro de tipo tabela pode fornecer um contrato explícito. Para resultados intermediários modificáveis compartilhados com trabalho dinâmico, uma tabela temporária externa costuma ser simples.

Nomes de colunas dinâmicos são outro problema. Um esquema de saída diferente a cada requisição dificulta instruções estáticas posteriores e código cliente. Considere retornar diretamente o resultado dinâmico ou representar atributos variáveis como linhas com colunas estáveis. Valide e delimite corretamente qualquer identificador construído.

Uma tabela temporária local pertence a uma sessão SQL física. Duas chamadas da aplicação não têm garantia de receber a mesma conexão do pool. Manter uma tabela para a próxima requisição web é uma estratégia de estado frágil, mesmo que um teste com um usuário funcione.

Evitar compartilhamento involuntário

Trocar #Work por ##Work cria uma tabela temporária global, mudando visibilidade e duração. Isso não apenas prolonga uma variável local. Requisições concorrentes podem colidir pelo nome ou observar dados umas das outras. Para trabalho entre requisições, uma tabela persistente com identificador único de job costuma oferecer um contrato mais claro.

Evite repetir nomes de tabelas temporárias em escopos aninhados. O SQL Server pode manter objetos locais homônimos em diferentes contextos, tornando a resolução surpreendente. Use nomes distintos para responsabilidades distintas, sem depender de qual objeto uma referência acaba encontrando.

Armazenamento temporário também é limitado. Linhas largas, índices e sessões longas podem ocupar tempdb por bastante tempo. Remova intermediários grandes após seu último uso, especialmente quando uma procedure continua com outras tarefas. Considere ainda como o rollback afeta os dados inseridos.

Teste o caminho real da aplicação com procedures aninhadas, lotes dinâmicos, erros e conexões separadas do pool. A correção útil define um proprietário e uma duração para os dados. Com essa fronteira clara, erros de objeto passam a ser previsíveis, em vez de parecerem falhas aleatórias de execução.

Referências técnicas: Microsoft Learn: CREATE TABLE and temporary scope · Microsoft Learn: sp_executesql.