Generar T-SQL dinámico SQL Server

Ventajas de generar T-SQL dinámico

La principal ventaja que nos aporta generar T-SQL dinámico, es su versatilidad. Gracias a esta práctica, podremos generar un código reutilizable. Es especialmente útil en tareas recurrentes, como la generación de informes, o la automatización de una tarea de base de datos.

Para generar el código, usaremos sp_executesql. Este procedimiento, es la evolución natural de EXEC. Ambos permiten ejecutar código, pero sp_executesql es más seguro, ya que se tiene que indicar el tipo de datos en los parámetros, a diferencia de EXEC, que solo funciona como cadena de texto, siendo este más vulnerable a inyección de código.

Ejemplo simple sin parámetros

Pongamos un ejemplo. Mensualmente, se requiere generar un informe en el que aparezcan las tablas de una base de datos. Normalmente usaríamos la siguiente query:

SELECT * FROM sys.tables;

Si ejecutamos esta query, nos devolverá información sobre las tablas que hay en la base de datos actual. Para ejecutarlo con sp_executesql, ejecutamos:

DECLARE @info_tablas nvarchar(100) = 'SELECT * FROM sys.tables';
EXEC sp_executesql @info_tablas;

Analizando el código vemos que se ha creado una variable (@info_tablas) y su tipo de datos: nvarchar(100), en la que se ha guardado la SELECT anterior. Posteriormente se ha ejecutado con sp_executesql.

Añadir más variables

Ahora que sabemos el funcionamiento, vamos a añadir parámetros para que realmente sea útil.

En el siguiente ejemplo, generaremos un código para obtener la información de una tabla específica, en este caso, jobs.

En primer lugar declararemos las dos variables, separadas por una coma, y le asignaremos un tipo de dato. nvarchar(100) para la consulta y nvarchar(10) para el nombre de la tabla. Con esto evitamos ataques por inyección de código, ya que estamos limitando los campos.

Posteriormente, pasamos los parámetros a sp_executesql con el siguiente orden:

1. Primera variable (@consulta)
2. Segunda variable (@tabla)
3. Definimos un valor para la variable. Para ello igualamos @tabla, para que coja el valor previamente definido.

DECLARE @consulta nvarchar(100) = 'SELECT * FROM sys.tables ' +
'WHERE name like ''%'' + @tabla + ''%''', 
@tabla nvarchar(10) = 'jobs';

EXEC sp_executesql @consulta, N'@tabla nvarchar(10)', @tabla =@tabla;

Si queremos cambiar el valor de la variable tabla, podríamos indicárselo en la propia línea de ejecución de sp_executesql. Para ello, deberíamos remplazar @tabla = @tabla, por la tabla que queremos ver, por ejemplo, sizes:

No debemos olvidar, que previamente, debemos declarar la variable @tabla.

Esperamos que este artículo os haya sido de utilidad.

Nos vemos en próximas entradas. Si no quieres perderte ninguna, suscríbete a nuestra newsletter. Con solo un email al mes, estarás informado, de todo lo que publiquemos.

Fuente: https://www.databasejournal.com/features/mssql/executing-dynamic-sql-using-spexecutesql.html