Geração de SQL dinâmico com o comando EXEC

O comando EXEC[UTE] executa uma string T-SQL ou um módulo. Entende-se aqui por módulo:

• system stored procedure,
• user-defined stored procedure,
• scalar-valued user-defined function,
• extended stored procedure.

Tem privilégios para a execução de um módulo a conta de utilizador que o criou (owner) ou aquela que detém permissão de execução (EXECUTE) sobre o módulo.


Sintaxe para execução de módulos

[EXEC[UTE]] [@return_status =] {module_name | @module_name_var} {[[@parameter1 =] value | [@parameter1=] @variable [OUTPUT]] | DEFAULT}.. [WITH RECOMPILE]

Sintaxe para execução de string T-SQL

EXEC[UTE] ({<string variable> |'<literal command string>'})

O comando EXEC permite construir ou invocar comandos dinamicamente em tempo de execução.

Há, no entanto, alguns factores a ter em consideração:

• O comando EXEC corre num contexto diferente do código que o invoca com excepção do @@ROWCOUNT (i.e., o código que invoca o EXEC não pode referenciar variáveis instanciadas no contexto do EXEC e da mesma maneira o EXEC não pode referenciar variáveis do código que o invoca depois de já incorporadas na string do EXEC)
• O EXEC corre no contexto de segurança do utilizador corrente e não no contexto de segurança do objecto que o invoca.
• O EXEC corre no contexto da transacção do objecto que o invoca.
• Uma concatenação que implique a chamada a uma função tem de ser executada sobre a string T-SQL antes de invocar o EXEC propriamente dito
• O EXEC não pode ser usado numa user-defined function.

Exemplos

Exemplo de execução de string T-SQL

DECLARE @InVar varchar(200)

SET @InVar = 'DECLARE @OutVar varchar(50) SELECT @OutVar = Nome FROM PessoaSegura WHERE PessoaSeguraID = 1 SELECT ''Nome obtido: '' + @OutVar'

EXEC (@Invar)

Exemplo de execução de string T-SQL e @@ROWCOUNT

EXEC('SELECT * FROM Cliente')
SELECT 'O Rowcount é ' + CAST(@@ROWCOUNT as varchar)

Exemplo de execução de string T-SQL com chamada a função

DECLARE @NumberOfLetters AS int

SET @NumberOfLetters = 3

DECLARE @str AS varchar(255)

SET @str = 'SELECT LEFT(''Ola mundo!'',' + CAST(@NumberOfLetters AS varchar) + ')'

EXEC(@str)

Exemplo de execução de módulos

SET NOCOUNT ON

CREATE TABLE #CurrentHistory (
RowID int IDENTITY(1, 1), 
ProcedureToRun varchar(64)
)


DECLARE @NumberRecords int, @RowCount int, @return_status int
DECLARE @ProcedureToRun varchar(64), @ErrorMsg varchar(104)

INSERT INTO #CurrentHistory (ProcedureToRun)
select [name] from .sys.all_objects
where type = 'P'
and name like 'Procedures a encontrar'


SELECT @NumberRecords = @@ROWCOUNT, @RowCount = 1

WHILE @RowCount <= @NumberRecords
BEGIN

SELECT @ProcedureToRun = ProcedureToRun 
FROM #CurrentHistory
WHERE RowID = @RowCount

EXEC @return_status = @ProcedureToRun

IF @return_status = 1
BEGIN

SELECT @ErrorMsg = 'Error on ' + @ProcedureToRun + ' execution. Its work was not completed. Please verify.'

RAISERROR (@ErrorMsg,16, 1)

END

SET @RowCount = @RowCount + 1

END

DROP TABLE #CurrentHistory

Referência: EXECUTE (Transact-SQL)



Relacionado: Difference between sys.sp_execute sys.sp_executesql

Licença CC BY-SA 4.0 Silvia Pinhão Lopes, 19.2.18