Using case statement in where clause

Other SQL Server Topics
1

Retrieving records based on Variables. I have 3 variables, based on which ones are selected will change the criteria for meeting search criteria.

@QCIncluded
@RMIncluded
@IsoIncluded

There is also a 'root' or basic list of systems that are always returned.

Based on that, I have the criteria set for each possible combination of options.

DECLARE @qcIncluded AS BIT = 1
DECLARE @rmIncluded AS BIT = 1
DECLARE @isoIncluded AS BIT = 1

SELECT [SystemName], [SampleDateTime], [AnalysisName], [AnalysisValueText]
FROM [CustomReporting].[ValueTbl]
WHERE
	[UnitNumber] = 1
	AND
		CASE 
			WHEN @qcIncluded = 0 AND @rmIncluded = 0 AND @isoIncluded = 0 THEN
				[SystemTypeID] IN (1,38,40,42,43,44,45,46,48,49,51,52,53,54,55,58,60,61,62) --Root
			WHEN @qcIncluded = 1 AND @rmIncluded = 0 AND @isoIncluded = 0 THEN
				[SystemTypeID] IN (1,3,37,38,39,40,42,43,44,45,46,48,49,51,52,53,54,55,58,60,61,62) --Root + QC
			WHEN @qcIncluded = 0 AND @rmIncluded = 1 AND @isoIncluded = 0 THEN
				[SystemTypeID] IN (1,2,38,40,42,43,44,45,46,48,49,51,52,53,54,55,58,60,61,62,63) --Root + RM
			WHEN @qcIncluded = 0 AND @rmIncluded = 0 AND @isoIncluded = 1 THEN
				[SystemTypeID] IN (1,38,40,42,43,44,45,46,48,49,51,52,53,54,55,57,58,60,61,62) --Root + ISO
			WHEN @qcIncluded = 0 AND @rmIncluded = 0 AND @isoIncluded = 0 THEN
				[SystemTypeID] IN (1,2,3,37,38,39,40,42,43,44,45,46,48,49,51,52,53,54,55,58,60,61,62,63) --Root + QC + RM
			WHEN @qcIncluded = 0 AND @rmIncluded = 0 AND @isoIncluded = 0 THEN
				[SystemTypeID] IN (1,3,37,38,39,40,42,43,44,45,46,48,49,51,52,53,54,55,57,58,60,61,62) --Root + QC + ISO
			WHEN @qcIncluded = 0 AND @rmIncluded = 0 AND @isoIncluded = 0 THEN
				[SystemTypeID] IN (1,2,38,40,42,43,44,45,46,48,49,51,52,53,54,55,57,58,60,61,62,63) --Root + RM + ISO
			WHEN @qcIncluded = 0 AND @rmIncluded = 0 AND @isoIncluded = 0 THEN
				[SystemTypeID] IN (1,2,3,37,38,39,40,42,43,44,45,46,48,49,51,52,53,54,55,57,58,60,61,62,63) --Root + QC + RM + ISO
		END

Receiving an error 'near the keyword 'IN'

How do I implement the CASE properly?

2

The CASE expression in SQL is not a control-of-flow construct like in C-based languages, it's more like a function and can only return a value.

Also, you have the same condition repeated multiple times:

WHEN @qcIncluded = 0 AND @rmIncluded = 0 AND @isoIncluded = 0

That would only be evaluated once, and return the first value of the sequence, so the last 4 options you have listed will never get evaluated.

The better way to do this is to instead create a table with the qc, rm and iso values as columns, plus a system type column:

CREATE TABLE criteria(qc bit not null
,rm bit not null
,iso bit not null
,systemType int not null
,CONSTRAINT PK_criteria PRIMARY KEY(qc,rm,iso,systemType));

INSERT criteria(qc,rm,iso,systemType) VALUES
(0,0,0,1),
(0,0,0,38),
(0,0,0,40),  -- include 1 row for each remaining systemType matching the qc=0, rm=0, iso=0 match
(1,0,0,1),
(1,0,0,3),
(1,0,0,37),  -- repeat each systemType for qc=1, rm=0, iso=0 match

-- include all values for each appropriate qc, rm, iso and systemTypes from the WHEN clauses

Once that table is populated, your query becomes:

DECLARE @qcIncluded AS BIT = 1
DECLARE @rmIncluded AS BIT = 1
DECLARE @isoIncluded AS BIT = 1

SELECT [SystemName], [SampleDateTime], [AnalysisName], [AnalysisValueText]
FROM [CustomReporting].[ValueTbl] a
INNER JOIN criteria c ON a.SystemTypeID=c.systemType
WHERE a.[UnitNumber] = 1
AND c.qc=@qcIncluded
AND c.rm=@rmIncluded
AND c.iso=@isoIncluded

The added benefit is that you can simply add, delete or update rows in your criteria table without having the rewrite the query.

3

But also, this syntax is invalid. The THEN section of the CASE expression is supposed to return a value. It's possible that in other database engines, X IN (list) will evaluate to a boolean TRUE or FALSE, but in SQL Server, such expression evaluations will not be done in this context... it belongs in the WHEN section.

WHEN @qcIncluded = 0 
 AND @rmIncluded = 0 
 AND @isoIncluded = 0 
 AND [SystemTypeID] IN (list of values)
THEN 1 -- or whatever value you intend to return here