Posteado por: martinmelchior | octubre 22, 2006

SQL Server – sp_executesql


Desde hace un tiempo tenía intenciones de llevar adelante algún proyecto personal donde aplicar C#. La idea era desarrollar algo de utilidad que me sirviera en mis proyectos personales y porque no en mi trabajo. Me puse a pensar y si bien existen montones de generadores de código dando vueltas en la web, decidí desarrollar algo sencillo que me automatice la generación de los sps (stores procedures), la DAL (en C#) y además utilice el CTP del EntLib de Enero de este año, específicamente el Data Access Application Block.

Respecto a los sps, el objetivo era automatizar la creación de sps para Insert, Update, Delete y Search, aunque quería potenciar y darle una mayor versatilidad al sp Search. Este sp es el que utilizo a la hora de ir a buscar registros que deben cumplir con una serie de condiciones.

Plasmando la idea: Con una estructura de tabla como esta.

  • ProductoId INT null
  • ProductoDes VARCHAR(50) null
  • ProductoPrecio NUMERIC(10,2) null
  • ProductoFecVto VARCHAR(30) null
  • ProductoObs VARCHAR(8000) null
  • ProductoUniqueKey Uniqueidentifier null
  • ProductoActivo BIT null

Para generar un Store tipo Search que me diera versatilidad, opté hacer algo por el estilo.

— Operadores posibles de utilizar
— <> | > | >= | < | <= | = | %_ | _% | %% | []
————————————————————
CREATE PROCEDURE dbo.Productos_Search

  • @OrdenDeRegistros VARCHAR(100) = null,
  • @OpeProductoId VARCHAR(2) = null,
  • @ProductoId INT = null,
  • @OpeProductoDes VARCHAR(2) = null,
  • @ProductoDes VARCHAR(50) = null,
  • @OpeProductoPrecio VARCHAR(2) = null,
  • @ProductoPrecio NUMERIC(10,2) = null,
  • @OpeProductoFecVtoOferta VARCHAR(2) = null,
  • @ProductoFecVtoOfertaDesde VARCHAR(30) = null,
  • @ProductoFecVtoOfertaHasta VARCHAR(30) = null,
  • @OpeProductoObs VARCHAR(2) = null,
  • @ProductoObs VARCHAR(8000) = null,
  • @OpeProductoUniqueKey VARCHAR(2) = null,
  • @ProductoUniqueKey VARCHAR(36) = null,
  • @OpeProductoActivo VARCHAR(2) = null,
  • @ProductoActivo BIT = null

Veamos los parámetros que recibe el sp Seach.
Como podemos observar, el primer parámetro nos permite definir el orden con el que se retornarán los registros desde la fuente de datos. Esta lista de campos debe ir separada por comas.

Ej: Productos_Search “ProductoId Desc, ProductoDes Asc”

Por otro lado, notar que antes de cada campo se puede enviar un operador (excepto los campos DateTime y SmallDateTime quienes reciben 2 operadores, para poder consultar un período de fechas), cuyos valores pueden ser, (<> , > , >= , < , <= , = , %_ , _% , %% , [] ) dependiendo del tipo de campo. Este operador es el que da versatilidad al sp Search.

Veamos algunos ejemplos:

Ej 1: Retornar productos cuyo Id es >= 10 (notar que no usamos orden).

  • Productos_Search null, “>=”, 10

Ej 2: Retornar productos tipo AA de segunda mano cuyo precio sea inferior a $ 1500 (notar que no usamos orden).

  • Productos_Search null, null, null, “%%”, “AA”, “<”, 1500, null, null, null, “%%”, “segunda mano”

Ej 3: Retornar productos tipo AA cuya oferta dure este mes (notar que usamos orden).

  • Productos_Search “ProductosDes DESC”, null, null, “%%”, “AA”, null, null, null, null, null, null, null, “[]”, “20061001”, “20061030”

Hasta aquí nada nuevo, aunque por una cosa u otra algo me llevó a leer sobre sp_executesql. Este sp que existe creo desde la versión 7 de SQL Server, es el que se recomienda para ejecutar cadenas Transact-SQL, en lugar de una instrucción EXECUTE.

Este procedimiento almacenado permite la sustitución de parámetros (es decir nosotros le indicamos a SQL Server cuáles son los parámetros, sus tipos y los valores en la cadena Transact-SQL a ejecutar), lo cuál lo hace mucho más versátil que EXECUTE.

Por otro lado y lo más importante, es que sp_executesql genera planes de ejecución con mayores probabilidades de que SQL Server los utilice nuevamente. Esto se debe a la similitud de las cadenas Transact-SQL generadas por sp_executesql, siendo así más eficaz que EXECUTE (mayor performance).

Teniendo en mente la estructura de la tabla Productos, imagine un store procedure que genere un Transact-SQL según la cantidad de parámetros que reciba.
Es decir, supongamos que el sp recibe solo el ProductoId, entonces va a generar y ejecutar un Transact-SQL como este:

  • EXECUTE “select * from productos where ProductoId = 10”

Pero si además del ProductoId recibe la Descripción, entonces el sp generará y ejecutará:

  • EXECUTE “select * from productos where ProductoId = 10 and ProductoDes like ‘%heladera%’ ”

Bien, para estos casos seguramente SQL Server no utilice los planes de ejecución de la caché porque la cadena Transact-SQL que está ejecutando difiere bastante una de otra.

Ahora bien, tenemos alguna solución para esta situación ?

Si, la idea es utilizar sp_executesql haciendo algo parecido a lo que mostramos aquí:

  • EXEC sp_executesql @sql, @parametros, @ProductoId, @ProductoDes

Ampliando el ejemplo:

SET NOCOUNT ON
SET DATEFORMAT DMY

—————————————-
— Definimos Variables
—————————————-
DECLARE @sql nvarchar(4000)
DECLARE @parametros nvarchar(4000)

SET @sql = N’ SELECT * FROM Productos WITH (NOLOCK) WHERE 1 = 1 ‘

— ProductoId = TYPE Int

IF (@OpeProductoId IS NOT NULL AND @OpeProductoId <> ” AND @ProductoId IS NOT NULL)
BEGIN
IF @OpeProductoId = ‘=’ OR @OpeProductoId = ‘<>‘ OR @OpeProductoId = ‘>=’ OR @OpeProductoId = ‘>’ OR @OpeProductoId = ‘<=' OR @OpeProductoId = '<'

SET @sql = @sql + ‘ AND ProductoId ‘ + @OpeProductoId + ‘ @ProductoId ‘
END

— ProductoDes = TYPE VarChar

IF (@OpeProductoDes IS NOT NULL AND @OpeProductoDes <> ” AND @ProductoDes IS NOT NULL)
BEGIN
IF @OpeProductoDes = ‘=’ OR @OpeProductoDes = ‘<>‘ OR @OpeProductoDes = ‘>=’ OR @OpeProductoDes = ‘>’ OR @OpeProductoDes = ‘<=' OR @OpeProductoDes = '<'
SET @sql = @sql + ‘ AND ProductoDes ‘ + @OpeProductoDes + ‘ @ProductoDes ‘

IF @OpeProductoDes = ‘%%’
SET @sql = @sql + ‘ AND ProductoDes LIKE ”%” + @ProductoDes + ”%” ‘

IF @OpeProductoDes = ‘_%’
SET @sql = @sql + ‘ AND ProductoDes LIKE @ProductoDes + ”%” ‘

IF @OpeProductoDes = ‘%_’
SET @sql = @sql + ‘ AND ProductoDes LIKE ”%” + @ProductoDes ‘
END

SELECT @parametros = ‘@ProductoId INT, @ProductoDes VARCHAR(50)’

EXEC sp_executesql @sql, @parametros, @ProductoId, @ProductoDes

Resumiendo: Utilizando sp_executesql logramos que SQL-Server utilice los planes de ejecución de la caché, inclusive en aquellos casos en los que los “valores” de los parámetros difiere y también en los que la “cantidad” de parámetros a utilizar no es la misma.
En mi caso particular es algo que voy a empezar a tener en cuenta en los sp que escriba (yo o la herramienta que utilice), ya que todo sp además de hacer lo que debe hacer, debe realizarlo en el menor tiempo posible.

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Categorías

A %d blogueros les gusta esto: