T-SQL Conditional WHERE Clause

Question

Found a couple of similar questions here on this, but couldn't figure out how to apply to my scenario.

My function has a parameter called @IncludeBelow. Values are 0 or 1 (BIT).

I have this query:

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

If @IncludeBelow is 0, i need the query to be this:

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. 

If @IncludeBelow is 1, that last line needs to be excluded. (i.e don't apply filter).

I'm guessing it needs to be a CASE statement, but can't figure out the syntax.

Here's what i've tried:

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) 

Obviously that's not correct.

What's the correct syntax?

Answer 1

I changed the query to use EXISTS because if there's more than one location associated with a POST, there'd be duplicate POST records that'd require a DISTINCT or GROUP BY clause to get rid of...

The non-sargable

This will perform the worst of the possible solutions:

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) 

The sargable, non-dynamic version

Self explanitory....

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 

The sargable, dynamic version (SQL Server 2005+):

Love or hate it, dynamic SQL lets you write the query once. Just be aware that sp_executesql caches the query plan, unlike EXEC in SQL Server. Highly recommend reading The Curse and Blessings of Dynamic SQL before considering dynamic SQL on 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