después de leer este artículo, aprenderá los conceptos básicos de un SQL dinámico, cómo construir sentencias basadas en valores de variables y cómo ejecutar sentencias construidas usando sp_executesql y EXECUTE() desde un procedimiento almacenado.
todos los ejemplos encontrados en esta lección se basan en Microsoft SQL Server Management Studio y las bases de datos de muestra de AdventureWorks y WideWorldImporters. Puede comenzar a usar estas herramientas gratuitas usando mi guía, comenzando a usar SQL Server.,
compilar SQL dinámico en un procedimiento almacenado.
muchos SQL que escribimos se escriben explícitamente en el procedimiento almacenado. Esto es lo que llamamos el SQL estático. Se llama así porque no cambia. Una vez que está escrito, eso significa que está martillado en piedra.
a continuación se muestra un ejemplo de un SQL estático:
SELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1970GROUP BY JobTitleSELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1971GROUP BY JobTitle
¿notó que hay dos sentencias aquí? Cada Estado de cuenta devuelve un resumen de los títulos de trabajo para un año de nacimiento de empleado específico. Si queremos agregar más años de nacimiento, entonces necesitamos agregar más declaraciones., ¿Y si solo tuviéramos que escribir la Declaración una vez y ser capaces de cambiar el año sobre la marcha?
Aquí es donde el SQL dinámico entra en juego.
SQL dinámico es SQL que se crea y ejecuta en tiempo de ejecución. Suena complicado, pero realmente no lo es. En lugar de tener las declaraciones escritos directamente en el procedimiento almacenado, las sentencias SQL son los primeros construidos y definidos en las variables.
el código en estas variables es entonces ejecutado. Ahora, continuemos con nuestro ejemplo, aquí está el mismo código usando el SQL dinámico:
el SQL dinámico está resaltado en verde., Este es el SQL que se construye para cada @ birthYear. A medida que se construye el SQL, se almacena en la instrucción@. Luego se ejecuta usando sp_executesql, que explicaremos a continuación.
Introducción a sp_executesql
puede usar sp_executeslq para ejecutar el SQL transact almacenado dentro de una variable. El formulario de declaración es
EXECUTE sp_executesql @statement.
en caso de que se lo pregunte, sp_executesql es un procedimiento almacenado en el sistema. Los procedimientos almacenados en el sistema amplían el lenguaje y proporcionan más funciones para que las utilice.,
Aquí hay un ejemplo simple para probar:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
Si ejecuta esto en la ventana de consulta, obtendrá un resultado similar como este:
2018-01-24 18:49:30.143
Ahora que ha visto cómo funciona sp_executeslq, pongámoslo en práctica. Supongamos que se le ha pedido que escriba un procedimiento de tienda que devuelva el promedio de LineTotal o la suma de LineTotal por ProductID para los productos enviados en 2011.
Su Jefe preferiría tener esto escrito como un procedimiento almacenado. El procedimiento almacenado debe aceptar un parámetro @ReturnAverage., Si es verdadero, entonces devolverá el promedio, de lo contrario la suma.
Por supuesto, podría escribir esto como dos consultas separadas como se muestra en el siguiente proceso almacenado, pero eso no sería muy divertido, ya que sería demasiado escribir y propenso a errores!
CREATE PROCEDURE uspCalcuateSalesSummaryStatic@returnAverage bitASIF (@returnAverage = 1)BEGIN SELECT SOD.ProductID, AVG(SOD.LineTotal) as ResultAvg FROM Sales.SalesOrderDetail SOD INNER JOIN Sales.SalesOrderHEader SOH ON SOH.SalesOrderID = SOD.SalesOrderID WHERE YEAR(SOH.ShipDate) = 2011 GROUP BY SOD.ProductIDENDELSEBEGIN SELECT SOD.ProductID, SUM(SOD.LineTotal) as ResultSum FROM Sales.SalesOrderDetail SOD INNER JOIN Sales.SalesOrderHEader SOH ON SOH.SalesOrderID = SOD.SalesOrderID WHERE YEAR(SOH.ShipDate) = 2011 GROUP BY SOD.ProductIDEND
la parte mala aquí es que hay una gran cantidad de código duplicado, que he coloreado de verde. No hay mucho código único, pero que hay, es de color rojo.
con toda esta redundancia, tenemos una gran oportunidad de mostrar algo de SQL dinámico. ¡Vamos a por ello!,
aquí, en lugar de tener dos versiones completas del SQL, una para AVG y otra para SUM, construimos la versión solicitada sobre la marcha.
el SQL se construye y guarda en la instrucción variable@. Esta variable se construye en base al valor del parámetro @returnAverage. Si se establece en 1, entonces la función @representa el promedio; de lo contrario, suma.
Puede ver dónde se construye el SQL para crear la instrucción. Fíjate en el código de colores. Debería corresponder partes similares dentro de la versión estática; esto debería ayudarle a hacer una comparación.,
depuración de SQL dinámico
puede preguntarse cómo se ve el SQL en tiempo de ejecución. Puede inspeccionar fácilmente el código utilizando el depurador:
ejecute el procedimiento almacenado utilizando el comando run del depurador y, a continuación, entre en el código
continúe entrando en el código hasta que lea la instrucción Execute resaltada a continuación.
Una vez que llegue a esta instrucción, pase el cursor sobre la instrucción @y cuando se muestre la sugerencia de herramienta, seleccione visualizador de texto.,
el depurador es potente y vale la pena entenderlo. Si le animaría mucho a aprender más sobre él aquí.
usando sp_executesql con parámetros
puede usar sp_executesql para hacer referencia a parámetros dentro de su instrucción. En última instancia, esto hace que su código sea más fácil de leer y proporciona algunos beneficios de optimización, ya que la instrucción se puede compilar una vez y reutilizar muchas veces.
La instrucción toma la forma:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
Así que vamos a explicar las piezas.
- @statement es el SQL que queremos ejecutar.,
- @parameterDefinition es una cadena que contiene una definición de todos los parámetros referenciados en la instrucción@. Se muestra cada parámetro y tipo found @statement. El nombre y el tipo están separados por un espacio. Varios parámetros están separados por una coma.
a continuación establecemos los valores de los parámetros, especificando los parámetros y el valor deseado. Los parámetros se enumeran en el orden definido en la cadena @parameterDefinition.
- @parm1 es el primer parámetro definido dentro de la cadena @parameterDefinition. Valor es el valor que desea establecer.,
- @parm2, es el segundo parámetro, si define, como se declara en @parameterDefinition.
- Y así sucesivamente
Aquí hay un ejemplo simple, que agrega dos números, para probar:
Las diversas porciones de la instrucción están codificadas por colores:
- @statement (Verde) – observe que incluye 2 parámetros: @a y @B. también observe que estos no están declarados en TSQL. Más bien, se definen en la definición del parámetro.
- @parameterDefinition (azul) – cada parámetro listado se define como type int.
- Parameter values (rojo) – establecemos el valor de los parámetros aquí.,
para terminar, en este ejemplo tenemos una instrucción SQL ejecutada dinámicamente que agrega dos parámetros.
Estos parámetros se definen como enteros. El valor de cada parámetro se establece en el comando sp_executesql.
ejemplo usando sp_executesql con parámetros
tomemos nuestro ejemplo anterior y extendámoslo. En lugar de codificar el shipDate en la consulta como lo hicimos, vamos a traer eso como un parámetro. Esto hace que la consulta sea más flexible y funcione con años distintos de 2011.,
para realizar este cambio, agregaremos un parámetro a nuestro procedimiento almacenado, así como la consulta dinámica. Usaremos el comando sp_executesql para llamar a la consulta dinámica usando estos parámetros.
el procedimiento almacenado actualizado con cambios se muestra a continuación. El parámetro procedimiento almacenado es verde y el parámetro consulta dinámica es Rojo.
para ejecutar esto, simplemente llame al uspcalculatesalessummarydynamic2 proc desde una consulta de windows utilizando el siguiente comando:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
si lo hace, verá estos resultados.,
permítanme mostrarles una multa de simplificación, vamos a combinar @shipDateYear y @shipDate en un parámetro. Eliminaremos @shipDateYear de nuestro código. Esto hace que sea más fácil seguir y leer:
observe que la instrucción EXECUTE es mucho más simple, no hay necesidad de asignar el parámetro de instrucción SQL @shipDateYear al valor del parámetro de procedimiento store @shipDate.
Esto hace que la declaración sea más compacta y fácil de leer., El flujo parece leer mejor, ya que no tiene que hacer conexiones mentales entre los parámetros de procedimiento almacenados y los parámetros SQL
Ejecutar SQL dinámico con EXECUTE ()
También puede usar el comando EXEC o EXECUTE para ejecutar SQL dinámico. El formato para este comando es
EXECUTE (@statement)
Aquí hay un ejemplo simple para probar:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE (@statement)
es importante incluir la instrucción @entre paréntesis. Si la instrucción EXECUTE no toma la instrucción@, y en lugar de ejecutar el SQL dinámico, piensa que el valor de la variable es el nombre de un procedimiento almacenado., Obtendrá el siguiente error:
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
por supuesto, esto proporciona una gran sugerencia! Si lo desea, puede usar variables para especificar qué procedimientos almacenados llamar.
sp_executesql versus EXECUTE
Puede que te estés preguntando por qué usar sp_executesql versus EXECUTE. ¿Cuál es la diferencia entre los dos?
Aquí hay varias razones por las que Microsoft recomienda usar sp_executesql para ejecutar SQL dinámico:
- Con ejecutar todos los parámetros mucho convertir de su tipo nativo a Unicode., Esto dificulta la capacidad del optimizador para hacer coincidir el SQL construido dinámicamente con un plan preexistente.
- Mediante el uso de sp_executesql, el optimizador reconoce los parámetros dentro del SQL dinámico, lo que facilita que el optimizador coincida con los planes.
- Es más fácil leer consultas parametrizadas que leer un montón de texto concatenado que las incorpora.
- Las consultas parametrizadas son menos propensas a ataques de inyección SQL.