MS SQL - Como tener un WHERE Dinámico

A quien no le ha pasado; al menos como programador claro!;  que cuando tenemos un formulario de consulta y este cuenta con varios filtros. Muchos de estos no son obligatorios pero, no ingresarlos afecta aun así el resultado de nuestra búsqueda.

Pues una buena solución a este problema es ejecutar este sentencian dentro de nuestro famoso Execute; pero claro este debe de ser una cadena de texto.

Veamos un ejemplo práctico con un procedimiento almacenado que nos devuelva los registros de la tabla Customers al cual llamaremos usp_verClientes

CREATE PROC usp_verClientes

@CustomerID NCHAR(5)=NULL,

@CompanyName NVARCHAR(40)=NULL,

@Country NVARCHAR(15)=NULL

AS

BEGIN

      SELECT *

      FROM Customers

      WHERE CustomerID=@CustomerID AND CompanyName=@CompanyName AND Country=@Country

END

GO

--Provar Procedimiento Almacenado

Exec dbo.usp_verClientes

@CustomerID ='ANTON',

@CompanyName ='Antonio Moreno Taquería',

@Country ='Mexico'

Pues bien, nuestro procedimiento cumple hasta el momento PERO ¿Qué sucedería si no pasáramos algunos parámetros?

--Probando el Sp Sin pasar algunos Parametros

Exec dbo.usp_verClientes

@CustomerID =NULL,

@CompanyName =NULL,

@Country ='Mexico'

Claro! Como era de imaginarse además que nuestra consulta está fija para que pacemos todos parámetros. Una alternativa seria jugando con el OR en lugar del AND.

Sin embargo, esto no se ajusta siempre a nuestra necesidad y sobre todo a veces queremos que solo filtre en el WHERE solo los parámetros que pasamos y el resto haga caso omiso. Veamos entonces como resolveríamos este con un Tic no tan bueno pero si una posible alternativa.

 --Modificando el SP para una consulta Dinámica

ALTER PROC usp_verClientes

@CustomerID NCHAR(5)=NULL,

@CompanyName NVARCHAR(40)=NULL,

@Country NVARCHAR(15)=NULL

AS

DECLARE @SQLCMD NVARCHAR(MAX)=' WHERE 1=1 '

BEGIN

      --Comprobando si tiene contenido el parametro

      If LEN(@CustomerID)>0 Set @SQLCMD =@SQLCMD  + ' AND CustomerID='''+@CustomerID +''''

      If LEN(@CompanyName)>0 Set @SQLCMD =@SQLCMD  + ' AND CompanyName='''+@CompanyName+''''

      If LEN(@Country)>0 Set @SQLCMD =@SQLCMD  + ' AND Country='''+ @Country +''''

     

      --Ejecutando el String

      EXEC ('SELECT * FROM Customers ' +@SQLCMD)   

END

GO

--Probando el Sp Sin pasar algunos Parametros con el procedimiento modificado

Exec dbo.usp_verClientes

@CustomerID =NULL,

@CompanyName =NULL,

@Country ='Mexico'

Estoy más que seguro que muchos me discutirán y dirán ¡Tío estás loco  o que! Utilizar esto y quedarse vulnerable al Sql Inyection?? Bues hay maneras simpáticas que corregir o mejor dicho prevenir a los usuarios mal intencionados uno es a través de una buena función de comprobación de parámetros ya sea por cualquier lenguaje de programación o por alguna función propia de SQL (T-SQL).

Saludos y espero que les haya ayudado un poquito.