Is it possible to use "IN" within a case statement SQL Server?-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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.