SQL Dinámico: Exec y sp_executeSql

La ejecución dinámica de instrucciones SQL es algo a lo que tarde o temprano (más bien temprano) tenemos que enfrentarnos. Y ya que tenemos que hacerlo más vale saber como funciona y tener un poco de cuidado… (ver artículo de inyección de código)

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

Tipos de SQL dinámico
Siempre es difícil hacer una clasificación, pero en este caso vamos a intentar diferenciar el SQL dinámico que se crea en el servidor y el que creamos en nuestras aplicaciones.
Por ejemplo si trabajamos en Visual Basic NET y en nuestro código tenemos algo como esto:

Dim sSQL as string
Dim entrada as string
Dim miCon As New SqlConnection(“cadena conexión”)
‘de alguna manera el usuario escribe una condición 
‘y metemos ese valor en la variable entrada
sSQL=”SELECT * FROM Clientes WHERE Nombre =“
sSQL=sSQL + “’” + entrada + “`”
Dim cmdProductos As New SqlCommand(sSQL, miCon)
cmdProductos.CommandType = CommandType.Text
miCon.Open()
. . . =cmdProductos.ExecuteReader()
‘ y todo lo que haga falta para usar los datos devueltos . . .

Esto sería SQL dinámico generado en el cliente.
Pero no es esto lo que nos interesa. Nosotros vamos a centrarnos en el SQL generado en el servidor, generalmente dentro de un procedimiento almacenado.
Primero veamos como podemos pasar el código anterior a un procedimiento almacenado que recibe un parámetro:

CREATE PROCEDURE VerProductos @Nombre varchar(50) AS
SELECT * FROM Productos WHERE Nombre = @ArticleID
GO

¿Más o menos lo mismo no? Pues sí, pero no.
La diferencia está en que cuando creamos la sentencia desde el cliente podemos hacer prácticamente de todo para editar y construir esa sentencia. Y cuando lo hacemos un procedimiento almacenado tenemos limitaciones a la hora de utilizar variables para construir nuestra sentencia.
Supongamos que necesitamos una sentencia SQL donde la parte dinámica es el nombre de la tabla. Parece lógico escribir algo así:

CREATE PROCEDURE ElegirTabla @NombreTabla VarChar(128) AS
SELECT * FROM @NombreTabla
GO

Pero si lo ejecutamos lo único que obtenemos es un error.

Incorrect syntax near '@NombreTalba'

¡No podemos utilizar variables en cualquier parte de una sentencia! Por ejemplo no podemos utilizarlas en la cláusula FROM. 

Ejecutar SQL dinámico con EXEC
Ahora es cuando ya tenemos que utilizar SQL dinámico de servidor propiamente dicho. Para solucionar el error de antes generaremos dentro de nuestro procedimiento almacenado una sentencia en una variable de texto como hacíamos desde Visual Basic NET y luego le diremos al SQL Server que ejecute esa sentencia mediante el comando EXEC.

CREATE PROCEDURE ElegirTabla @NombreTabla VarChar(128) AS
DECLARE @sSQL VarChar(1000)
--El truco esta en tener la sentencia dentro de una variable
SELECT sSQL = 'SELECT * FROM ' 
SELECT @sSQL = @SQL + @NombreTabla
--Y ejecutar ese texto contenido en nuestra variable
EXEC ( @sSQL)
GO

Como vemos es sencillo, pero utilizar el SQL así dentro de un procedimiento almacenado nos ocasiona dos problemas.
En primer lugar utilizar así los parámetros da lugar a problemas de seguridad porque estamos permitiendo que el usuario utilice trucos de inyección de código y que nuestro procedimiento almacenado haga cosas para las que no fue creado.
Un ejemplo de lo que alguien podría llegar a hacer es:

EXEC ElegirTabla ' Clientes GO DROP TABLE Pedidos'

Que un usuario pueda ejecutar sentencias como esta no es algo muy deseable verdad.

El segundo problema que nos plantea el uso de EXEC es de rendimiento. Como ya sabemos una de las ventajas de los procedimientos almacenados es que cuando se ejecuta la primera vez se compila, y no es necesario recompilarlo en ejecuciones sucesivas. Pero esto deja de ser cierto cuando usamos EXEC para ejecutar SQL dinámico porque cada ejecución requiere una compilación.
Esto nos lleva al segundo modo de ejecutar SQL dinámico dentro de un procedimiento almacenado. 

SQL dinámico con SP_EXECUTESQL
El procedimiento 'sp_executesql’ puede utilizarse como alternativa a los procedimientos almacenados para ejecutar varias veces una instrucción de Transact-SQL si la única modificación es que cambian los valores de los parámetros. Como dicen los BOL, al permanecer constante la propia instrucción SQL y variar sólo los valores de los parámetros, es probable que el optimizador de consultas del SQL Server vuelva a utilizar el plan de ejecución que genera para la primera ejecución.
El funcionamiento de sp_executesql es el siguiente:

sp_executesql [@stmt =] stmt
[ 
    {, [@params =] N'@parameter_name  data_type [,...n]' } 
    {, [@param1 =] 'value1' [,...n] }
]

Usar este procedimiento almacenado soluciona en gran parte los dos problemas que veíamos con la ejecución dinámica a través de EXEC.
Por un lado lo único que se transmite a sp_executesql son los parámetros y eso evita la inyección de código, y por otro lado se puede reutilizar el plan de ejecución.
Veamos un ejemplo de cómo utilizar este procedimiento.

USE NorthWind
DECLARE @sSQL nvarchar(1000) 
--la variabla que contiene la cadena a ejecutar debe ser unicode, por eso lo de nvarchar
SET @sSQL = 'SELECT * FROM Products WHERE ProductName = @NombreProducto'
EXEC sp_executesql @sSQL, N'@NombreProducto nvarchar(50)', @NombreProducto = 'chai'

Como vemos el primer parámetro es la sentencia que queremos ejecutar, después viene declarada la variable que vamos a usar y por último le damos valor a esa variable.
Seguro que se os ocurren un par de utilidades para sp_executesql.  

Más sobre SP_EXECUTESQL
Sp_executesql tiene unas cuantas peculiaridades que conviene tener en cuenta.
Para empezar la ejecución de una sentencia a traves de sp_executesql se hace en un ámbito propio. Esto quiere decir que las variables declaradas fuera no se pueden utilizar, que las declaradas dentro no se pueden leer desde fuera, que podemos cambiar la base de datos (utilizar USE) en la que trabajar dentro del sp_executesql, crear tablas temporales en el nuevo ámbito, utilizar sentencias SET sin  hacer cambios en la configuración del procedimiento llamante, y por último aunque no menos importante que si se produce un error y se aborta la instrucción también se aborta el procedimiento en el que se está ejecutando.

En cuanto a los permisos recordar algo muy importante. La sentencia SQL se ejecuta dentro del contexto de seguridad del usuario, no del procedimiento que llama a esa sentencia.

Un ejemplo de cómo usar y como no usar SQL dinámico
Para concluir vamos a ver un ejemplo donde podemos usar SQL dinámico.
A veces tenemos que crear una sentencia que ordene una tabla según una columna que dependerá de algún parámetro que nos pasa el usuario. Eso lo podemos resolver utilizando un procedimiento almacenado y una sentencia como:

CREATE PROCEDURE OrdenarPor @Columna varchar(50) AS
DECLARE @sSQL varchar(50)
SET @sSQL=’SELECT * FROM MiTabla ORDER BY ’ + @Columna
EXEC @sSQL

Esto está en la línea de todo lo que hemos estado diciendo.
Es una solución sencilla pero que como no nos hemos cansado de repetir plantea varios problemas de seguridad y rendimiento. Así que ¿por que no pensamos un poco e intentamos crear un procedimiento almacenado que haga lo mismo?
Creedme que pocas veces encontraremos una tarea que no se pueda resolver sin recurrir al SQL dinámico.
Fijémonos en lo siguiente:

CREATE PROCEDURE OrdenarPor @Columna varchar(50) AS
SELECT * FROM   miTabla ORDER  BY 
CASE @Columna
                  WHEN 'columna1' THEN columna1
                  WHEN 'columna2' THEN columna2
                  WHEN 'columna3' THEN columna3
               END

Cierto que es un poco más complicado (se utiliza la función CASE), pero no hay inyección de código posible y una vez calculado el plan de ejecución se utilizará sin recompilar una y otra vez

Como conclusión nos queda que el SQL dinámico es una herramienta potente pero peligrosa si no se trata con cuidado, y que la mayoría de las veces no necesitamos recurrir a ella porque siempre podemos encontrar otra solución.