EXEC vs sp_executesql

Después de 8 meses de ser parte de la comunidad de Microsoft en Línea, me he dado cuenta que con el paso del tiempo mi percepción y elaboración procedimientos almacenados que requieran la ejecución de un QUERY DINAMICO  ha cambiado y gran parte de esto se debe a la comprensión de los comandos posibles de utilizar para este fin.

Un tema tal vez ya muy comentado y muy discutido es este sobre todo por el riesgo que conlleva la ejecución a través de estos medios ya que no se recomienda la ejecución de algún QUERY DINAMICO, después de una pequeña explicación, dejaré a su criterio con respecto a cuál es la mejor opción.

A través de ambas sentencias podemos ejecutar una instrucción SQL, inclusive una serie de comandos como los que a continuación les muestro:

EXEC

1.- Una simple consulta

EXEC('SELECT * FROM Sales.Store')

2.- Construir la cadena SQL y ejecutarla posteriormente.
a)

DECLARE @sql NVARCHAR(1000)

DECLARE @campos NVARCHAR(100)

DECLARE @CreditRating INT

SET @CreditRating = 1

IF @CreditRating = 1

 SET @campos = N'Name'

ELSE

 SET @campos = N'AccountNumber'

EXEC('SELECT '+ @campos +' FROM Purchasing.Vendor')

b)

DECLARE @sql NVARCHAR(1000)

DECLARE @campos NVARCHAR(100)

DECLARE @CreditRating INT

SET @CreditRating = 2

IF @CreditRating = 1

 SET @campos = N'Name'

ELSE

 SET @campos = N'AccountNumber'

EXEC('SELECT '+ @campos +' FROM Purchasing.Vendor')

3.- Un procedimiento almacenado, aunque en ocasiones no se requiere de la sentencia EXEC para ejecutar este, basta con nombrarlo y presionar F5 o sobre el botón Execute.

EXEC sp_who

sp_who

Para introducir parámetros, para los valores de tipo cadena, es necesario agregar los delimitadores correspondientes, para muchos que comienzan con esto les pueda generar un dolor de cabeza.

DECLARE @AccountNumber NVARCHAR(100)

SET @AccountNumber = 'EN'

EXEC('SELECT * FROM Purchasing.Vendor WHERE AccountNumber LIKE ''%' + @AccountNumber + '%''' )

DECLARE @EntityIDa NVARCHAR(100)

DECLARE @EntityIDB NVARCHAR(100)

SET @EntityIDa = '1500'

SET @EntityIDb = '1515'

EXEC('SELECT * FROM Purchasing.Vendor WHERE BusinessEntityID BETWEEN ' + @EntityIDa + ' AND ' + @EntityIDb )

4.- Inclusive en una ocasión revisando una base de datos descubrí un procedimiento almacenado que creaba otro procedimiento almacenado, lo ejecuta y antes de terminar la ejecución del primero eliminaba el que fue creado, no comprendo la razón de dicha operación. Algo más o menos así:

exec('

create procedure holaMundo

as

select ''Hola Mundo'' as campo

')

5.- También es posible ejecutar comandos para creación de tablas, cambiar de usuario, entre muchas otras:

exec('

CREATE TABLE [dbo].[prueba](

       campo1 [int] IDENTITY(1,1) NOT NULL,

       campo2 [datetime] NOT NULL,

       campo3 [sysname] NOT NULL

)

')

Ahora bien, su base de datos puede estar expuesta a la Inyección de Código SQL Malicioso ( SQL INJECTION ), les muestro un ejemplo:

Primero creamos un procedimiento almacenado:

CREATE PROCEDURE prueba

       @param1 VARCHAR(100)

AS

              DECLARE @sql NVARCHAR(1000)

       SET @sql = 'SELECT * FROM Person.Address WHERE StateProvinceID = ' + @param1 + ' '

       EXEC( @sql )

Y luego lo ejecutamos

EXEC prueba '79'

Hasta este punto no hay problema, pero que pasa si hacemos lo siguiente:

EXEC prueba '79;DELETE FROM Person.Address'

Es un parámetro válido y borrará la tabla correspondiente por lo que hay que tener mucho cuidado con su uso. 

sp_executesql

Algunos ejemplos de lo que se puede realizar con el procedimiento almacenado sp_executesql:

1.- Una consulta:

EXEC sp_executesql N'SELECT * FROM Person.Address'

2.- Construir la cadena SQL y ejecutarla posteriormente:
a)

DECLARE @sql NVARCHAR(1000)

DECLARE @campos NVARCHAR(100)

DECLARE @CreditRating INT

SET @CreditRating = 1

IF @CreditRating = 1

       SET @campos = N'Name'

ELSE

       SET @campos = N'AccountNumber'

SET @sql ='SELECT '+ @campos +' FROM Purchasing.Vendor'

EXEC sp_executesql @sql

b)

DECLARE @sql NVARCHAR(1000)

DECLARE @campos NVARCHAR(100)

DECLARE @CreditRating INT

SET @CreditRating = 2

IF @CreditRating = 1

       SET @campos = N'Name'

ELSE

       SET @campos = N'AccountNumber'

SET @sql ='SELECT '+ @campos +' FROM Purchasing.Vendor'

EXEC sp_executesql @sql

Para poder realizarlo con parámetros es un poco más complejo que EXEC solo que en este caso no es necesario de agregar los delimitadores de cadena, basta con realizar la declaración de parámetros de la manera adecuada, un ejemplo:

DECLARE @PrmEntityIDa INT

DECLARE @PrmEntityIDb INT

DECLARE @PrmModifiedDate DATE

DECLARE @PrmName VARCHAR(10)

DECLARE @sql NVARCHAR(1000)

SET @PrmEntityIDa = 1500

SET @PrmEntityIDb = 1600

SET @PrmModifiedDate = '20060305'

SET @PrmName = 'Bike'

SET @sql = N'SELECT * FROM Purchasing.Vendor WHERE BusinessEntityID BETWEEN @EntityIDa AND @EntityIDb AND ModifiedDate = @ModifiedDate AND CHARINDEX(@Name,Name)<>0'

EXEC sp_executesql @sql, N'@EntityIDa INT, @EntityIDb INT, @ModifiedDate DATE,@Name VARCHAR(10)', @PrmEntityIDa, @PrmEntityIDb, @PrmModifiedDate,@PrmName

Un poco más compleja pero es más seguro, porque no es necesario poner delimitadores para cada tipo de dato en la cadena que pretendemos ejecutar.  También podemos extraer valores, utilizando OUTPUT.

DECLARE @PrmEntityIDa INT

DECLARE @PrmEntityIDb INT

DECLARE @PrmModifiedDate DATE

DECLARE @PrmName VARCHAR(10)

DECLARE @ValorCount INT

DECLARE @sql NVARCHAR(1000)

SET @PrmEntityIDa = 1500

SET @PrmEntityIDb = 1600

SET @PrmModifiedDate = '20060305'

SET @PrmName = 'Bike'

SET @sql = N'SELECT @ValorSalida=COUNT(AccountNumber) FROM Purchasing.Vendor WHERE BusinessEntityID BETWEEN @EntityIDa AND @EntityIDb AND ModifiedDate = @ModifiedDate AND CHARINDEX(@Name,Name)<>0'

EXEC sp_executesql @sql, N'@EntityIDa INT, @EntityIDb INT, @ModifiedDate DATE,@Name VARCHAR(10),@ValorSalida INT OUTPUT', @PrmEntityIDa, @PrmEntityIDb, @PrmModifiedDate,@PrmName,@ValorSalida = @ValorCount OUTPUT

SELECT @ValorCount  

Cabe señalar que ambas son buenas herramientas, pero es necesario identificar cuando debemos utilizar cada una, ya que de no hacerlo, estaremos exponiendo la seguridad de nuestras bases de datos. En el foro precisamente leí sobre una persona que tuvo acceso a una base de datos, en ocasiones no es necesario tener acceso a la base con el usuario y contraseña basta con que existan huecos de seguridad como el QUERY DINAMICO para poder inyectar el código y causar grandes destrozos.

Ustedes que opinan? lo dejo a su criterio.

COMPARTE ESTA INFORMACION SI TE PARECIO INTERESANTE