Con
cierta frecuencia debemos recurrir a algunas tecnicas de desarrollo que nos
permiten optimizar nuestro trabajo en una forma simple y mantenible, en este caso
particular tiene que ver con el diseño de un procedimiento almacenado que me
permitiera hacer una consulta cuyas condiciones estaran basadas en los
parametros de entrada, delimitando o filtrando los resultados.
El
ejemplo basico que utilizaremos usa la Base de Datos Northwind, y consiste en
obtener todos los registros de la tabla Orders que coincidan con los filtros
relacionados a: Codigo de Empleado, Codigo de Cliente y Via de Compra
(ShipVia).
CREATE PROCEDURE uspGetOrders
(
@CustomerID
nchar(5)=null,
@EmployeeID
int=null,
@ShipVia
int=null
)
As
SELECT * FROM dbo.Orders
WHERE (CustomerID=COALESCE(@CustomerID,CustomerID))
AND (EmployeeID=COALESCE(@EmployeeID,EmployeeID))
AND (ShipVia=COALESCE(@ShipVia,ShipVia))
Set @tabla=’Products’
En este
segmento de código podemos observar que se reciben los parametros antes
mencionados, pero teniendo un default value igual a null, en caso de que no se
envie un parametro este sera obviado en el procesamiento o filtro de la
consulta.
Al
momento de establecer las condiciones del Where por ejemplo: CustomerID=COALESCE(@CustomerID,CustomerID))
Estamos
indicando que la columna CustomerID sera igual al valor retornado por la
funcion Coalesce la cual nos retorna el primer valor no nulo, por lo que en
caso de que el parametro sea diferente de nulo se tendra conformada la
condicion, y en caso de ser null entonces procede a establecer CustomerID=CustomerID,
esto representa una condicion de igualdad, lo que no causa ningun procesamiento
y sirve perfectamente a nuestro objetivo.
Cuando
procedemos a ejecutar el sp pasamos los valores a determinados parametros según
nuestras necesidades. Como ejemplo:
EXEC uspGetOrders
@CustomerID=’PICCO’,
@EmployeeID=2
Nos devuelven 3 registros.
O
EXEC uspGetOrders
@CustomerID=’PICCO’
Nos devuelve 10 registros
Esta
solucion a mi parecer resulta mas simple y menos compleja que otras en las que
utilizan IF o instrucciones CASE WHEN.
Me parece muy buen método, no se me había ocurrido. Lástima que como lo presentas solo funciona cuando tienes defaults null.
Cuando tienes defaults de valores distintos a null, puede lograrse el mismo efecto combinando las instrucciones nullif y coalesce::
Where CustomerID=COALESCE(NULLIF(@CustomerID,0),CustomerID)
De esta forma tienes el mismo comportamiento pero en vez de null uso el cero como valor «null».
Lo que yo normalmente hago es lo siguiente:
Where (CustomerId ==0 OR CustomerId=@CustomerID)
Es cosa de gustos, la forma que tu presentas es más fácil de entender, pero la segunda (que siempre he usado) permite más opciones:
Where
(@CustomerCountry == «USA» OR (@CustomerCountry = CustomerCountry AND TaxType=»Foreign»))
AND (@CustomerCountry <> «USA» OR (@CustomerCountry = CustomerCountry AND TaxType=»Local»))
Esta forma se aprovecha de la evaluación selectiva de condicionales: En un OR si la primera parte es true la segunda no se evalúa. En un AND si la primera condición en false la segunda parte no se evalúa.
En el ejemplo, la primera instrucción indica que si el pais es distinto de usa, se seleccione solo aquellos con el pais entregado por parámetro y además se seleccione solo aquellos con taza de tarifa extranjera.
La segunda hace lo contrario, si el pais ES usa, se aplica busca solo aquellos con taza local.
Es complicado de seguir, pero es efectivo para aquellas consultas que como dices, normalmente se harían con CASE o peor, con tablas temporales o hasta cursores.
Seria igual a esto?:
SELECT * FROM dbo.Orders
WHERE (CustomerID = @CustomerID or @CustomerID is null)
AND (EmployeeID = @EmployeeID or @EmployeeID is null)
AND (ShipVia = @ShipVia or @ShipVia is null)
Cual de ambas seria mejor optimizada por el motor? y en consecuencia tiempo de respuesta a la consulta mejor.
Saludos,
Hola estimados colegas, el fin de este pequeño articulo era mostrar un metodo alternativo que permitiera de forma ordenada y sencilla realizar consultas dinamicas, partiendo del hecho que los parametros que serian utilizados como argumento pueden ser o no enviados desde un aplicativo.
El uso de la tecnica mostrada a traves del uso de WHERE (CustomerID=COALESCE(@CustomerID,CustomerID))
si bien es funcional, tiene una condición para su aplicación y es el hecho de que los campos incluidos en el filtro no deben permitir valores nulos, ya que en caso de tenerlo generara un conjunto vacio, este aspecto y otros relacionados con este tema se encuentran ampliamente documentados en el siguiente articulo de Erland Sommarskog
La opcion mas factible es la que presenta Peppe, pero no por su rendimiento que en terminos comparativos es igual al uso de Coalesce, sino por el hecho de que no restringe los campos a filtrar a que no acepten valores nulos.
Cualquier consulta mi correo es geohernandez@gmail.com
Saludos