T-SQL Condicional Cláusula WHERE

Encontré un par de preguntas similares aquí sobre esto, pero no pude averiguar cómo aplicar a mi escenario.

Mi función tiene un parámetro llamado @IncludeBelow. Los valores son 0 o 1 (BIT).

Tengo esta pregunta: 

SELECT p.*
FROM Locations l
INNER JOIN Posts p
on l.LocationId = p.LocationId
WHERE l.Condition1 = @Value1
AND   l.SomeOtherCondition = @SomeOtherValue

Si @IncludeBelow es 0, necesito que la consulta sea la siguiente: 

SELECT p.*
FROM Locations l
INNER JOIN Posts p
on l.LocationId = p.LocationId
WHERE l.Condition1 = @Value1
AND   l.SomeOtherCondition = @SomeOtherValue
AND   p.LocationType = @LocationType -- additional filter to only include level.

Si @IncludeBelow es 1, esa última línea debe excluirse. (es decir, no aplicar filtro).

Supongo que tiene que ser una declaración CASE, pero no puedo entender la sintaxis.

Esto es lo que he intentado: 

SELECT p.*
FROM Locations l
INNER JOIN Posts p
on l.LocationId = p.LocationId
WHERE l.Condition1 = @Value1
AND   l.SomeOtherCondition = @SomeOtherValue
AND (CASE @IncludeBelow WHEN 0 THEN p.LocationTypeId = @LocationType ELSE 1 = 1)

Obviamente eso no es correcto.

¿Cuál es la sintaxis correcta?

Author: OMG Ponies, 2010-12-20
Source

3 answers

Cambié la consulta para usar EXISTE porque si hay más de una ubicación asociada con un POST, habría registros de POST duplicados que requerirían una cláusula DISTINCT o GROUP BY para deshacerse de...

El no sargable

Esto llevará a cabo la peor de las soluciones posibles: 

SELECT p.*
  FROM POSTS p
 WHERE EXISTS(SELECT NULL
                FROM LOCATIONS l
               WHERE l.LocationId = p.LocationId
                 AND l.Condition1 = @Value1
                 AND l.SomeOtherCondition = @SomeOtherValue)
   AND (@IncludeBelow = 1 OR p.LocationTypeId = @LocationType)

La versión sargable, no dinámica

Autoexplicativo....

BEGIN
  IF @IncludeBelow = 0 THEN
    SELECT p.*
      FROM POSTS p
     WHERE EXISTS(SELECT NULL
                    FROM LOCATIONS l
                   WHERE l.LocationId = p.LocationId
                     AND l.Condition1 = @Value1
                     AND l.SomeOtherCondition = @SomeOtherValue)
       AND p.LocationTypeId = @LocationType
  ELSE
    SELECT p.*
      FROM POSTS p
     WHERE EXISTS(SELECT NULL
                    FROM LOCATIONS l
                   WHERE l.LocationId = p.LocationId
                     AND l.Condition1 = @Value1
                     AND l.SomeOtherCondition = @SomeOtherValue) 
END

La versión sargable, dinámica (SQL Server 2005+):

El amor o el odio, SQL dinámico le permite escribe la consulta una vez. Solo tenga en cuenta que sp_executesql almacena en caché el plan de consulta, a diferencia de EXEC en SQL Server. Recomendamos encarecidamente leer La maldición y las Bendiciones de Dynamic SQL antes de considerar dynamic SQL en SQL Server...

DECLARE @SQL VARCHAR(MAX)
    SET @SQL = 'SELECT p.*
                  FROM POSTS p
                 WHERE EXISTS(SELECT NULL
                                FROM LOCATIONS l
                               WHERE l.LocationId = p.LocationId
                                 AND l.Condition1 = @Value1
                                 AND l.SomeOtherCondition = @SomeOtherValue)'

    SET @SQL = @SQL + CASE 
                        WHEN @IncludeBelow = 0 THEN
                         ' AND p.LocationTypeId = @LocationType '
                        ELSE ''
                      END   

BEGIN 

  EXEC sp_executesql @SQL, 
                     N'@Value1 INT, @SomeOtherValue VARCHAR(40), @LocationType INT',
                     @Value1, @SomeOtherValue, @LocationType

END
 37
Author: OMG Ponies,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-01-18 23:49:46

Puedes escribirlo como

SELECT  p.*
  FROM  Locations l
INNER JOIN Posts p
    ON  l.LocationId = p.LocationId
  WHERE l.Condition1 = @Value1
    AND l.SomeOtherCondition = @SomeOtherValue
    AND ((@IncludeBelow = 1) OR (p.LocationTypeId = @LocationType))

Que es un patrón que se ve mucho, por ejemplo, para los parámetros de búsqueda opcionales. Pero IIRC que puede estropear los planes de ejecución de la consulta por lo que puede haber una mejor manera de hacer esto.

Dado que es solo un poco, casi podría valer la pena decidir entre dos bloques de SQL con o sin la comprobación, por ejemplo, utilizando un IF en un procedimiento almacenado o con diferentes cadenas de comandos en el código de llamada, basado en el bit?

 10
Author: Rup,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-12-20 00:30:41

Puede cambiar su instrucción CASE a esto. El planificador de consultas ve esto de manera diferente, pero puede no ser más eficiente que usar OR:

(p.LocationTypeId = CASE @IncludeBelow WHEN 0 THEN p.LocationTypeId ELSE @LocationType END)
 3
Author: Hai Phan,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-07-23 15:18:53