SQL Dinâmico em Stored Procedure (Building Dynamic SQL In a Stored Procedure) (Parte 1/3)

Introdução

O SQL Dinâmico em STORED PROCEDURE é uma declaração Transact-SQL simples ou um conjunto de instruções armazenadas em uma variável e executado através de um comando SQL. Pode haver vários métodos de aplicação no SQL Server. Este artigo irá mostrar-lhe um bom método de aplica isso. Antes de entrar em uma explicação detalhada, deixe-me perguntar "Quando usar SQL dinâmico?" Nós não podemos dizer definitivamente que o SQL estático (comando direto) irá atender todas as nossas necessidades de programação. O Dynamic SQL é necessário quando precisamos recuperar um conjunto de registros com base em parâmetros de pesquisa. Digamos, por exemplo - Uma tela de pesquisa empregado ou um relatório de propósito geral que precisa executar uma instrução e selecionar com base em uma cláusula WHERE diferente.

NOTA: Mais importante ainda, as consultas SQL dinâmicas em uma variável não são compilados, analisados, marcada por erros até que eles sejam executados.

sp_executesql Vs comando EXECUTE

Na construção  dinâmica  de Transact-SQL pode ser executado através do comando EXECUTE ou declaração sp_executesql. Aqui, neste artigo e nos meus exemplos, eu vou usar sp_executesql que é mais eficiente, mais rápido na execução e também apóia a substituição de parâmetro. Se estamos usando EXECUTE para executar a seqüência de SQL, todos os parâmetros devem ser convertidos em texto e concatenado na consulta antes da execução. O sp_executesql proporciona uma melhor maneira de implementar isso. Isso nos permite substituir os valores dos parâmetros para qualquer parâmetro especificado na string SQL. Antes de entrar no exemplo real, deixe-me diferenciar esses dois comandos com um exemplo simples. Consulta: selecionar um registro da tabela empregado utilizando o ID na cláusula WHERE.

A sintaxe básica do comando  EXECUTE:

EXECUTE(@SQLStatement)

A sintaxe básica para usar o sp_executesql:

sp_executesql [@SQLStatement],[@ParameterDefinitionList],
[@ParameterValueList]

Exemplo 1.0

/* Declaração de Variávies */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
/* Setando valor dos parâmetros */
SET @EmpID = 1001
/* Construindo o SQL com o parâmetro */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = ' + 
CAST(@EmpID AS NVARCHAR(10))
/* Executando a Transact-SQL */
EXECUTE(@SQLQuery)

No exemplo 1.0 acima, temos 2 variáveis declaras. A proimeira variável @EmpID é usada como parâmetro da procura e a segunda variável @SQLQuery é usada para construir a string SQL, a consulta propriamente dita. Pode-se perceber que essa variável @EmpID é convertida para NVarchar para fazer parte da string T-SQL. Se você imprimir a variável @SQLQuery  (PRINT @SQLQuery), você terá uma visualização aprecida com esta:

SELECT * FROM tblEmployees WHERE EmployeeID = 1001

No final a query acima será executada pelo comando EXECUTE




Exemplo 1.1




DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* Setando valores do parâmetro */
SET @EmpID = 1001
/* Contruindo o SQL incluindo os parâmetros */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID' 
/* Especificando o formato do parâmetro */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Executando o SQL */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

In the example 1.1, there are two variables declared. The variable @EmpID is used as a parameter to the SQL Query and second variable @SQLQuery is used to build the SQL String, the third variable @ParameterDefinition is used to specify the parameter format before executing the SQL string. If you print the @SQLQuery string (PRINT @SQLQuery), you will get the query as shown below:




SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID

Here, in this example, you can clearly see the parameter @EmpID is included in the statement. Finally, sp_executesqltakes the necessary information to do the parameter substitution and execute the dynamically built SQL string.

1. @SQLQuery --> contains the SQL statement
2. @ParameterDefinition --> contains the Parameter Definition
3. @EmpID --> contains the parameter value to be substituted to the parameter in the SQL statement.

NOTE: The parameters included in the Dynamic SQL string must have a corresponding entry in the Parameter Definition List and Parameter Value List.










---

Texto original em: http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure