Sql Server : Sentencia WHERE con condicionales

Se puede incluir sentencias condicionales en la clausula WHERE, de modo que según los parámetros se forma una condición en la consulta.

Escenario:

Usaremos la tabla Customers de la base de datos de ejemplo Northwind que viene con el Sql Server. La consulta se hará con lo siguiente:

1. Todos los registros que contengan la palabra "the" en el nombre de la compañía.
2. Todos los registros para compañías localizadas en Alemania, excluyendo las que empiecen con la letra "A".

Normalmente, se podría crear una sentencia dinamica consistente en multiples IF y cadenas a ser concatenadas a una variable final, la cual sería ejecutada por EXEC. Pero a veces es preferible otra opción mas dinamica, pero también posiblemente mas lenta.

El código

-- Declarar algunas variables locales.
-- Actualmente se crean dos por cada variable incluida en el WHERE.
-- La primera variable representa el valor que estamos filtrando
-- y la segunda el operador para el filtro.

declare @companyName varchar(255)
declare @companyNameOp varchar(2)
declare @country varchar(255)
declare @countryOp varchar(2)

-- Valores de ejemplo. Los valores son para el segundo escenario descrito
-- anteriormente, todos los registros para las compañías localizadas
-- en Alemania, excluyendo cuyos nombres comiencen con "A"

-- Definicion de los operadores
-- para el ejemplo, utilizamos un par de letras:

-- ne = no igual
-- eq = igual
-- bg = empieza con
-- ed = termina con
-- ct = contiene

-- Para este ejemplo se utiliza campos de tipo varchar
-- pero es sencillo adaptarlo para otros tipos de datos


set @companyname = 'A%'
set @companynameOp = 'ne'
set @country = 'Germany'
set @countryOp = 'eq'

-- Armemos la consulta.

select
   customerid, companyname, country
from
   customers
where
   case @companyNameOp
        when '' then 1 -- Operador no definido, lista todo
        when 'eq' then -- Operador es "igual"
             case when companyname like @companyName then 1 else 0 end
        when 'bg' then -- Operador es "comienza con"
             case when companyname like @companyName +'%' then 1 else 0 end
        when 'ed' then -- Operador es "termina con"
             case when companyname like '%' + @companyName  then 1 else 0 end
        when 'ct' then -- Operador es "contiene"
             case when companyname like '%' + @companyName  +'%' then
1 else 0 end
        when 'ne' then -- Operador es "no igual"
             case when companyname not like @companyName then 1 else 0 end end =1

AND

-- lo mismo es para el otro campo

    case @countryOp
         when '' then 1
         when 'eq' then
              case when country like @country then 1 else 0 end
         when 'bg' then
              case when country like @country +'%' then 1 else 0 end
         when 'ed' then
              case when country like '%' + @country  then 1 else 0 end
         when 'ct' then
              case when country like '%' + @country  +'%' then 1 else 0 end
         when 'ne' then
              case when country not like @country then 1 else 0 end
    end =1

Con esta consulta, se pueden crear procedimientos almacenados, mas flexibles, que permitiran unas consultas personalizadas más dinamicas.


Fuente:
Sql Server Central - Sotiris Filippidis