post icon

Crear Consultas SQL con filtros dinámicos de manera eficiente

Crear consultas SQL con filtros dinámicos siempre ha sido un tema controversial, especialmente en cuanto a que técnicas de implementación son mejores. Y es muy común utilizarlas en las áreas que se dedican al reporting.

Lo más común que se suele hacer es caer en el error de armar la query SQL de forma dinámica dentro de la aplicación, esto puede provocar fallos en la seguridad y problemas de rendimiento, otra forma común de implementar es con SQL Dinámico, que dependiendo de como se implementa también tiene problemas de seguridad y rendimiento, quizá en menor medida que la anterior forma si se utiliza el comando propio del motor para ejecutar una query SQL dinámica, si bien es cierto que esta técnica ofrece la máxima flexibilidad posible en cuanto a variabilidad de la query en sí, para el filtro dinámico no es necesaria tanta flexibilidad ya que sólo requerimos que varíen en la sección del WHERE, y no el cuerpo ni otras secciones de la query como serían las listas de tablas a implementar, la lista de salida, los grupos entre otros. Otra forma de implementar es utilizando LIKE por cada columna potencial de filtro, al menos esta técnica ya no afecta a la seguridad del sistema, pero si impacta gravemente en el rendimiento, debido a la necesidad potencial de ejecutar un LIKE ‘%’. Finalmente queda la forma que veremos más abajo, la utilización del COALESCE (ANSI SQL) o su equivalente dentro del lenguaje del motor utilizado.

Antes de avanzar más, quiero acotar varios puntos muy importantes que también tienden a repetirse. Si lo que necesitamos es tener una query con filtros dinámicos con una combinación de 2 o 3 columnas posibles, la mejor implementación es crear un procedimiento almacenado para cada combinación posible, se escribe tan sólo un poco más y no se pierde seguridad ni rendimiento (siempre y cuando el diseño y el mantenimiento de la BD estén a punto).

Cuando tengamos más columnas candidatas a actuar de filtro la combinación se vuelve muy grande y difícil de mantener si vamos a crear un procedimiento por cada uno, es allí donde les recomiendo que evaluen cuales son las combinaciones más utilizadas dentro de todas y/o cuales combinaciones tienen peor rendimiento y creen un procedimiento propio para dicha combinación, para el resto de los casos pueden utilizar esto

WHERE COLUMNA = COALESCE(@PARAMETRO, COLUMNA);

 

WHERE COLUMNA = COALESCE(:PARAMETRO, COLUMNA);

 

WHERE COLUMNA = COALESCE(@PARAMETRO, COLUMNA);

y luego solo van agregando más AND para los filtros de la misma forma. Por si alguno necesita más explicación sobre que sucedería, la función COALESCE evalúa una lista de valores y retorna el primero que encuentre que no sea nulo considerando el orden que le fue pasado, es decir, que para que esto funcione debidamente, deben pasarle al procedimiento todos los parametros de todas las columnas candidatas a filtro y si una en particular no debe ser considerada debe contener un valor NULL, lo que haría que se filtre su propio valor, algo como 1=1.

Comentarios desde Facebook:

  1. avatar
    Gerardo Ruiz Garcia MEXICO Mozilla Windows
    22 octubre 2014 at 22:12 #

    Declare
    @Valor_Buscar varchar(30)
    Exec (‘select * from [name_table] where campo like ‘ + ”’+@Valor_buscar+”’)

    Espero te sirva.

Responder