SQL dinámico en Transact SQL

Transact SQL permite dos formas de ejecutar SQL dinamico(construir sentencias SQL dinamicamente para ejecutarlas en la base de datos):

La instrucción EXECUTE - o simplemente EXEC
El procedimiento almacenado sp_executesql

Desde aquí recomendamos la utilización de sp_executesql si bien vamos a mostrar la forma de trabajar con ambos métodos.

La instrucción EXECUTE

La instrucción EXECUTE - o simplemente EXEC - permite ejecutar una cadena de caracteres que representa una sentencia SQL. La cadena de caracteres debe ser de tipo nvarchar .

El siguiente ejemplo muestra como ejecutar una cadena de caracteres con la instrucción EXEC.

 DECLARE @sql nvarchar(1000)
 
SET @sql = 'SELECT
		COD_PAIS,
		NOMBRE_PAIS,
		ACTIVO,
		FX_ALTA
	    FROM
		PAISES'
EXEC (@sql)

También con SQL dinamico podemos ejecutar sentencias de tipo DDL (Data Definition Languaje), como CREATE TABLE.

DECLARE @sql nvarchar(1000)
SET @sql='CREATE TABLE TEMPORAL
	( ID int IDENTITY, DATO varchar(100))'
EXEC (@sql)
 
SET @sql = 'SELECT * FROM TEMPORAL' 
EXEC (@sql)

El principal incoveniente de trabajar con la instrucción EXEC es que no permite el uso de parametros abriendo la puerta a potenciales ataques de Sql Injections - http://www.devjoker.com/contenidos/Articulos/45/Seguridad-en-Internet--SQL-Injections.aspx

Además el uso de la instrucción EXEC es menos eficiente, en terminos de rendimiento, que sp_executesql.

Para solventar el problema debemos trabajar siempre con sq_executesql, que permite el uso de parametros y con el que obtendremos un mejor rendimiento de nuestras consultas.

El procedimiento almacenado sp_executesql

Para ejecutar sql dinamico, se recomienda utilizar el procedimiento almacenado sp_executesql, en lugar de una instrucción EXECUTE.

sp_executesql admite la sustitución de parámetros
sp_executesql es más seguro y versátil que EXECUTE
sp_executesql genera planes de ejecución con más probabilidades de que SQL Server los vuelva a utilizar, es más eficaz que EXECUTE.

El siguiente ejemplo muestra el uso (muy simple) de sp_executesql.

DECLARE @sql nvarchar(1000)

SET @sql = 'SELECT

COD_PAIS,

NOMBRE_PAIS,

ACTIVO,

FX_ALTA

FROM

PAISES'

EXEC sp_executesql @sql

sp_executesql admite la sustitución de valores de parámetros para cualquier parámetro especificado en la cadena Transact-SQL a ejecutar.

El siguiente ejemplo muestra el uso de sp_executesql con parámetros:

 DECLARE @sql nvarchar(1000),
	@paramDefinition nvarchar(255),
	@paramValue char(3)
 
SET @paramDefinition = '@codPais char(3)' 
SET @paramValue = 'ESP'
SET @sql = 'SELECT
		COD_PAIS,
		NOMBRE_PAIS,
		ACTIVO,
		FX_ALTA
	    FROM
		PAISES
	    WHERE COD_PAIS = @codPais'
 
EXEC sp_executesql @sql, @paramDefinition, @paramValue