T-SQL Conditional WHERE Clause

问题:

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?

回答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

回答2:

You can write it as

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))

which is a pattern you see a lot e.g. for optional search parameters. But IIRC that can mess up the query execution plans so there may be a better way to do this.

Since it's only a bit, it almost might be worth deciding between two blocks of SQL with or without the check, e.g. using an IF in a stored procedure or with different command strings in calling code, based on the bit?

回答3:

You can change your CASE statement to this. The query planner sees this differently, but it may be no more efficient than using OR:

(p.LocationTypeId = CASE @IncludeBelow WHEN 0 THEN p.LocationTypeId ELSE @LocationType END)