Por que não posso usar variáveis ​​em T-SQL como imagino que posso?

De acordo com a página on-line de livros para variáveis

As variáveis ​​podem ser usadas apenas em expressões, não no lugar de nomes de objetos ou palavras-chave. Para construir instruções SQL dinâmicas, use EXECUTE.

Funcionaria da maneira que você esperava se, por exemplo, você usasse sua variável em uma cláusula where. Quanto ao motivo, acho que tem algo a ver com o analisador não ser capaz de avaliar a variável e, assim, verificar a existência. Ao executar, a consulta é analisada primeiro para sintaxe e objetos e, em seguida, se a análise for bem-sucedida, a consulta é executada no ponto em que a variável seria definida.

DECLARE @name varchar(20);
SET @name = 'test';

CREATE TABLE [#tmp]([val] varchar(10));

insert into #tmp
values('test')

SELECT *
FROM [#tmp]
WHERE [val] = @name;

As limitações no uso de variáveis ​​em instruções SQL surgem da arquitetura do SQL.

Existem três fases no processamento de uma instrução SQL:

1. Preparação - A instrução é analisada e um plano de execução é compilado, especificando quais objetos do banco de dados são acessados, como são acessados ​​e como estão relacionados. O plano de execução é salvo no cache do plano .
2. Vinculação - quaisquer variáveis ​​na instrução são substituídas por valores reais.
3. Execução - o plano em cache é executado com os valores vinculados.

O SQL Server esconde a etapa de preparação do programador e a executa muito mais rápido do que os bancos de dados mais tradicionais, como Oracle e DB2. É por motivos de desempenho que o SQL gasta potencialmente muito tempo determinando um plano de execução ideal, mas só o faz na primeira vez que a instrução é encontrada após uma reinicialização.

Portanto, no SQL estático , as variáveis ​​só podem ser usadas em locais onde não invalidarão o plano de execução, portanto, não para nomes de tabelas, nomes de colunas (incluindo nomes de colunas em condições WHERE), etc.

O SQL dinâmico existe para os casos em que não é possível contornar as restrições, e o programador sabe que levará um pouco mais de tempo para executar. O SQL dinâmico pode ser vulnerável à injeção de código mal-intencionado, portanto, tome cuidado!

Como você pode ver, a pergunta "por que" requer um tipo diferente de resposta, incluindo lógica histórica e suposições subjacentes para o idioma, não tenho certeza se posso realmente fazer justiça a isso.

Este artigo abrangente do SQL MVP Erland Sommarskog tenta fornecer alguma justificativa, juntamente com a mecânica:

A maldição e as bênçãos do SQL dinâmico :

Planos de Consulta de Cache

Cada consulta executada no SQL Server requer um plano de consulta. Quando você executa uma consulta pela primeira vez, o SQL Server cria um plano de consulta para ela – ou, como diz a terminologia – compila a consulta. O SQL Server salva o plano no cache e, na próxima vez que você executar a consulta, o plano será reutilizado.

Este (e segurança, veja abaixo) é provavelmente o maior motivo.

O SQL opera sob a premissa de que as consultas não são operações únicas, mas que serão usadas continuamente. Se a tabela (ou o banco de dados!) não for realmente especificada na consulta, não há como gerar e salvar um plano de execução para uso futuro.

Sim, nem todas as consultas executadas serão reutilizadas, mas essa é a premissa operacional padrão do SQL , portanto, "exceções" devem ser excepcionais.

Algumas outras razões listadas por Erland (observe que ele está listando explicitamente as vantagens de usar procedimentos armazenados , mas muitas delas também são vantagens de consultas parametrizadas (não dinâmicas)):

• O sistema de permissão : o mecanismo SQL não pode prever se você tem direitos para executar uma consulta se não conhecer a tabela (ou banco de dados) na qual estará operando. "Cadeias de permissão" usando SQL dinâmico é um pé no saco.
• Reduzindo o tráfego de rede : passar o nome do procedimento armazenado e alguns valores de parâmetro pela rede é mais curto do que uma instrução de consulta longa.
• Lógica de encapsulamento : você deve estar familiarizado com as vantagens de encapsular a lógica de outros ambientes de programação.
• Acompanhando o que é usado : se eu precisar alterar a definição de uma coluna, como posso encontrar todo o código que a chama? Existem procedimentos do sistema para encontrar dependências em um banco de dados SQL, mas somente se o código estiver em procedimentos armazenados.
• Facilidade de Escrever Código SQL : A verificação da sintaxe ocorre quando você cria ou modifica um procedimento armazenado, portanto, esperamos que menos erros resultem.
• Resolução de bugs e problemas : um DBA pode rastrear e medir o desempenho de procedimentos armazenados individuais com muito mais facilidade do que o SQL dinâmico em constante mudança.

Novamente, cada um deles tem uma centena de nuances que não vou abordar aqui.

Você precisa usar sql dinâmico

DECLARE @DatabaseName varchar(150) = 'dbamaint'
declare @sqltext nvarchar(max) = N''

set @sqltext = N'CREATE DATABASE '+quotename(@DatabaseName)+ ';'

print @sqltext 

-- once you are happy .. uncomment below
--exec sp_executesql @sqltext
set @sqltext = ''
set @sqltext = N'use '+quotename(@DatabaseName)+ ';'
print @sqltext 
-- once you are happy .. uncomment below
--exec sp_executesql @sqltext

abaixo está a saída de print .. assim que você descomentar exec sp_executesql @sqltextas instruções serão realmente executadas ...

CREATE DATABASE [dbamaint];
use [dbamaint];