Olá pessoal, recentemente expliquei este problema para uma outra pessoa e imaginei que fosse interessante para mais alguém
Vamos fazer alguns exercícios.
Considere uma query simples
SELECT COUNT(*) FROM SYSDATABASES
Resultado
----------- 152 (1 row(s) affected)
Até aqui sem problemas, vamos ver outro exercício
SET NOCOUNT ON SELECT COUNT(*) FROM SYSDATABASES SET NOCOUNT OFF
Resultado
----------- 152
Mais uma vez sem problemas, todos devem conhecer o “SET NOCOUNT ON” que elimina a contagem de registros.
Vamos agora ver um cenário similar ao anterior, só que desta vez vamos executar um comando com Query Dinamica
EXEC(' SET NOCOUNT ON ')
SELECT COUNT(*) FROM SYSDATABASES
Resultado
----------- 152 (1 row(s) affected)
Mas o que aconteceu? Ele não deveria ter respeitado o “SET NOCOUNT ON” ?
Vamos fazer outro teste
EXEC ('
SET NOCOUNT ON
CREATE TABLE #TEMP (x int);
INSERT INTO #TEMP VALUES (1),(2),(3)
SELECT * FROM #TEMP;
')
SELECT * FROM #TEMP;
Resultado
x ----------- 1 2 3 Msg 208, Level 16, State 0, Line 12 Invalid object name '#TEMP'.
Mais uma vez estranho ? Eu estou executando como uma unica consulta, ele não deveria encerrar a tabela temporária antes do fim da sessão ?
Será que o SQL cria uma outra sessão?
SET NOCOUNT ON
SELECT @@SPID
EXEC ('SELECT @@SPID')
Resultado
------ 146 ------ 146 Também não
Conclusão
O que acontece quando vc executa uma consulta dinâmica é que ele gera um contexto de sessão diferente do contexto da query principal, mas ainda ligada a sessão que a gerou
Se consultarmos o “PAI DOS BURROS” dos DBAs “Books Online” temos
Using EXECUTE with a Character String