0

My goal is to only select rows of a certain type that is dependent on the @Daily parameter, I am wondering whether its possible to use an IN statement within a case statement.. Below is how I've currently got this working in two separate statements, but as you can see most of the code is redundant other than the where clause of each statement.

IF @Daily = 1
SELECT DISTINCT Column001, Column002, Column003, Column004
        FROM            table1
        INNER JOIN      table2 ON table2.Ref = table1.Ref
        INNER JOIN      table3 ON table3.Ref= table2.Ref
        WHERE           table3.Type IN (1, 2, 3, 4)

IF @Daily = 0
SELECT DISTINCT Column001, Column002, Column003, Column004
        FROM            table1
        INNER JOIN      table2 ON table2.Ref = table1.Ref
        INNER JOIN      table3 ON table3.Ref= table2.Ref
        WHERE           table3.Type IN (5, 6, 7, 8)

This isn't valid syntax but I was wondering whether something like this was possible?

SELECT DISTINCT Column001, Column002, Column003, Column004
        FROM            table1
        INNER JOIN      table2 ON table2.Ref = table1.Ref
        INNER JOIN      table3 ON table3.Ref= table2.Ref
        WHERE           CASE WHEN @Daily = 1 THEN table3.Type IN (1, 2, 3, 4)
                             WHEN @Daily = 0 THEN table3.Type IN (5, 6, 7, 8)
                             END;

Is something like this possible? Unfortunately I can't add a daily flag onto the table itself for this task which would have bene nice to have.

Ted Burgess
  • 19
  • 3
  • 1
    You would be better off with two queries and benefitting from two potenially optimised execution plans; trying to mash conditions into the where clause can lead to a sub-optimal plan that's fit for neither. – Stu Oct 24 '22 at 20:04
  • Yes, you can as shown [here](https://stackoverflow.com/a/10260297/92546). Performance tends to suffer greatly since such queries tend not to be [sargable](https://en.wikipedia.org/wiki/Sargable). (With _parameter sniffing_ and recompilation some queries might perform well, but it's not a pretty construct.) – HABO Oct 24 '22 at 21:42

2 Answers2

1

Change the WHERE clause to:

WHERE CASE WHEN @Daily = 1 AND table3.Type IN (1, 2, 3, 4) THEN 1
           WHEN @Daily = 0 AND table3.Type IN (5, 6, 7, 8) THEN 1
           ELSE 0
      END = 1
The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • 3
    Is this complete? The CASE statement will produce either a 1 or 0 but isn't compared with anything and so I'd expect it to generate "An expression of non-boolean type specified in a context where a condition is expected, near 'END'." as written. – Ben Thul Oct 24 '22 at 20:20
  • This should probably return a value to compare with @Daily, although this predicate is non-sargable? – Stu Oct 24 '22 at 20:49
  • Nah, if you want sargable, you need two different queries / dynamic SQL. This is essentially the same as `WHERE (@Daily = 1 AND Type BETWEEN 1 AND 4) OR (@Daily = 0 AND Type BETWEEN 5 AND 8)` - you can get a seek with either of those clauses (assuming there is or can be a suitable index, anyway), but not once you add the OR. +1 because the logic does answer the question asked, even if there might be cases where an alternative might be better. – Stuck at 1337 Oct 24 '22 at 21:31
  • 1
    @BenThul My mistake. Added `= 1` at the end. – The Impaler Oct 25 '22 at 13:06
1

Getting a seek on the base table might be possible with the right supporting index (e.g. a covering index leading on Type).

One way is with dynamic SQL, like this:

DECLARE @Daily bit = 1;

DECLARE @command nvarchar(max) = N'SELECT <cols>
  FROM dbo.table3 
  WHERE Type IN (' + CASE @Daily
    WHEN 1 THEN '1,2,3,4' ELSE '5,6,7,8' END + N');';

EXEC sys.sp_executesql @command;

This is tougher to demonstrate due to fiddle behaviors, but this fiddle shows the seek based on a hard-coded instance of the query dynamic SQL would have executed.

That said, dynamic SQL is ugly and hard to maintain and, if you're not careful, prone to SQL injection.

Another way is to join to a constants table (here I'll just use a table variable, but no reason this couldn't be permanent and so not part of the query):

DECLARE @Daily bit = 1;

DECLARE @Constants table(Daily bit, Type int, 
  PRIMARY KEY(Daily, Type));

INSERT @Constants VALUES(1,1),(1,2),(1,3),(1,4),
                        (0,5),(0,6),(0,7),(0,8);

SELECT <cols>
  FROM dbo.table3 AS t3
  WHERE EXISTS
  (
    SELECT 1 
      FROM @Constants AS c
      WHERE c.Daily = @Daily
      AND c.Type = t3.Type
  );

This is demonstrated in this fiddle. Note that the seek on the constants table is far less important than the seek on the base table.

Stuck at 1337
  • 1,900
  • 1
  • 3
  • 13