SQL DINÁMICO

En ocasiones es necesario crear consultas dinámicamente dentro de un procedimiento almacenado, este proceso no es muy díficil de realizar como se puede ver en el pequeño ejemplo que se muestra a continuación, pero es importante tener cuidado con él porque puede ser un arma de doble filo.

USE AdventureWorks

CREATE PROCEDURE FilterCustomers

DECLARE @field VARCHAR(50), @filter VARCHAR(250) AS BEGIN

DECLARE @query VARCHAR(1000)

/*consulta dinámica*/ SELECT @query = 'SELECT * FROM Person.Contact WHERE ' + @field + '=' + @filter

EXEC(@query)

END

Esta técnica debe ser usada con mucho cuidado, ya que puede ser una puerta a la inyección de sql por parte del usuario. A través de las variables pasadas como parametros podemos introducir código malicioso y concatenarlo a la sentencia que estamos creando.

Otro problema con el que nos podemos encontrar, es que el rendimiento de la aplicación puede verse disminuido notablemente. Al usar EXEC() para ejecutar la consulta el procedimiento almacenado que la contenga no quedará compilado. Por lo tanto, tendrá que recompilarse cada vez que sea necesario su uso, ya que el optimizador de consultas del SQL Server no será capaz de volver a utilizar el plan de ejecución generado en el primer uso del procedimiento.

Estos dos problemas pueden solucionarse utilizando SP_EXECUTESQL en vez de EXEC(), ya que si la consulta permanece constante y sólo cambian los parametros de entrada el optimizador del SQL Server podrá reutilizar el plan de ejecución. Y además se solucionan los problemas de seguridad ya que se hace uso de parámetros.

USE AdventureWorks

CREATE PROCEDURE FilterCustomers DECLARE @field VARCHAR(50), @filter VARCHAR(250) AS BEGIN

DECLARE @query VARCHAR(1000)

/*consulta dinámica*/ SELECT @query = 'SELECT * FROM Person.Contact WHERE ' + quotename(@fiel)

+ ' = @filter'

EXEC sp_executesql @query, N'@filter NVARCHAR(250)',@filter=@filter

END

Nota: QUOTENAME: Devuelve una cadena Unicode con los delimitadores agregados para convertirla en un identificador delimitado válido de Microsoft SQL Server 2005.