Session + Query Dinamica

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

Changes in database context last only until the end of the EXECUTE statement. For example, after the EXEC in this following statement is run, the database context is master. 

 

Espero que tenham gostado