35

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
RPM1984
  • 72,246
  • 58
  • 225
  • 350

4 Answers4

43

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
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 1
    Very nice. I'm not a fan of DSQL (where it can be avoided), and the "sargable" solution would work but keep in mind the code i have supplied has been simplified for the purposes of the question. I have around 6 joins - (location to post isn't a directly relationship - there is a junction table - many to many). So i'd have to repeat all that in the `EXISTS` clause. Because of this, duplicates aren't an issue (no group by required). I didn't want to show my entire code because it would be too confusing to everyone. Thanks for answer - very insightful and complete (as always). +1 and accepted. – RPM1984 Dec 20 '10 at 00:52
  • Lolz, if you'd seen the original code you'd probably pull your hair out. I have temp tables, UDT's, three LOJ's to TVF's, CASE's, MAX's, the works. She ain't pretty. :) – RPM1984 Dec 20 '10 at 00:55
  • Does the `case` part of the dynamic sql seciton need to read `SET @SQL = @SQL + CASE...`. It needs to be joined to the original, right? – Brad Jan 18 '13 at 19:08
  • In the non-sargeable version, you're assuming boolean short-circuiting works which I've read isn't guaranteed. Are you 100% sure the code will *always* execute as expected? – U Avalos Feb 19 '14 at 19:37
13

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?

Rup
  • 33,765
  • 9
  • 83
  • 112
  • Yeah i thought about the IF, but in reality my query is a lot bigger. I don't want to have to maintain two massive blocks of T-SQL when all that is different is 1 line. Your above code works and performs fairly well. Will accept this answer in 7 minutes (unless someone comes up with a another answer explaining why theirs is better than this one). Thanks. – RPM1984 Dec 20 '10 at 00:35
  • That's not sargable -- it won't perform well. – OMG Ponies Dec 20 '10 at 00:37
  • 1
    @OMG Ponies Heh, I was just about to link to this old answer of yours http://stackoverflow.com/questions/3649036/how-to-handle-optional-parameters-in-sql-query/3649184#3649184 – Rup Dec 20 '10 at 00:39
  • I've been messing with that a bit here: http://stackoverflow.com/questions/4408551/select-statment-performance-degradation-when-using-distinct-with-parameters I've heard it can make a query non-SARGable, but the example is one of the few times I've seen it make a significant difference. – Kevin Stricker Dec 20 '10 at 00:39
  • @Rup - thanks for your answer, but i feel like i should accept @OMG Ponie's one because it's a more complete answer and shows the different options available. +1 to you too though. :) – RPM1984 Dec 20 '10 at 00:53
  • 1
    @RPM1984 Thanks! No, absolutely agree - and happy to learn the other ways of doing it myself. – Rup Dec 20 '10 at 01:04
4

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)
Sarsaparilla
  • 6,300
  • 1
  • 32
  • 21
0

Edit the sql statement as follows:

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

The @IncludeBelow variable is not needed

To include all location types Set @LocationType = '%'

To limit the location types returned by the query Set @LocationType = '[A Specific Location Type]'

The above Set statements assume the @LocationType variable is character datatype

Rich Capp
  • 1