Consultas dinámicas en SQL Server

Las consultas dinámicas de instrucciones de T-SQL es algo que tarde o temprano (más bien temprano) tenemos que enfrentarnos, y ya que tenemos que hacerlo es bueno saber cómo y no morir en el intento.

Por qué dinámico
Hablamos de ejecución dinámica porque la sentencia SQL que va a ejecutar nuestro servidor no está escrita en ningún procedimiento estáticamente, sino que se crea cada vez que tenemos que ejecutarla. Frente a esta forma de trabajar tenemos el SQL “normal”, donde la sentencia SQL está previamente escrita y compilada y lo único que hay que hacer es decidir cuando la ejecutamos.

Existen dos instrucciones que permiten la ejecución de sentencias SQL dinámicas:

EXEC

DECLARE @ConsultaSQL NVARCHAR(500)
DECLARE @Tabla NVARCHAR(25)

SET @Tabla = 'Empleados'
SET @ConsultaSQL = 'SELECT * FROM ' + @Tabla

EXEC(@ConsultaSQL)

En el código anterior se construye una consulta T-SQL dinámica para devolver todos los registros de una tabla llamada Empleados. Aunque por su sencillez no es del todo últil en el ejemplo propuesto; podríamos también enviar dinámicamente los criterios para filtrar desde nuestra aplicación usando la cláusula Where, Order By, etc; de esta manera tendríamos un solo procedimiento almacenado que mostraría los resultados según criterios seleccionados por el usuario en la capa de presentación.

Pero esta instrucción tiene una limitante; y es el hecho de que no permite el paso de parámetros. Para entender mejor ésto veamos el funcionamiento de la siguiente instrucción:

SP_EXECUTESQL

DECLARE @ConsultaSQL NVARCHAR(500)
DECLARE @Tabla NVARCHAR(25)
DECLARE @Edad1 INT
DECLARE @Edad2 INT

SET @Edad1 = 50
SET @Edad2 = 70

SET @Tabla = 'Empleados'
SET @ConsultaSQL = 'SELECT * FROM ' + @Tabla + ' WHERE Edad BETWEEN @Edad1 AND @Edad2'

--Ejecución de consulta dinámica
EXEC SP_EXECUTESQL @ConsultaSQL, N'@Edad1 INT, @Edad2 INT', @Edad1, @Edad2

La ejecución de esta instrucción se hace mediante el comando EXEC, ya que se trata de un procedimiento pre-compilado propio de SQL Server.

En este ejemplo se dinamiza además de la consulta, los parámetros del BETWEEN, los cuales son pasados dinámicamente a la consulta. Nótese que éstos deben definirse en el llamado del procedimiento, o bien podrían haberse definido en una variable, y usar ésta en el llamado.

SET @Parametros = N'@Edad1 INT, @Edad2 INT'
EXEC SP_EXECUTESQL @ConsultaSQL, @Parametros, @Edad1, @Edad2

El abanico de posibilidades al utilizar consultas dinámicas es grande, al igual que las ventajas, pero hay que tener mucho cuidado con las brechas abiertas respecto a la seguridad, ya que por ejemplo al tratarse de una aplicación web estas consultas pueden ser interceptadas y podríamos fácilmente ser víctimas de Inyección SQL, por lo que lo aconsejable es usar procedimientos almacenados con consultas definidas siempre que se pueda.

Desde este link pueden descargar la base de datos usada en los ejemplos.

Suerte !

«La función de un buen software es hacer que lo complejo aparente ser simple»