Switch case/If in a where clause

Question

I am trying to change the conditions of PART of a where based on some value. I have looked at similar articles on here including:

SQL Switch/Case in where clause

But they aren't quite dealing with my case.

What I have is a stored procedure that does a select based on its inputs. Those inputs are optional (or have default parameters as they prefer to call it).

2 of said parameters are numbers.

• if the first number only is specified then column X must equal that number.
• if the first and second number is specified then column X must be >= to first AND <= to second.
• if only the second number is specified it is INVALID.

This is what I tried (which obviously didn't work)

DECLARE @SECOND INT;
DECLARE @FIRST INT;

SET @FIRST = 123456;
SET @SECOND = 67890;

SELECT * FROM BANK_DETAIL 
WHERE -- POSSIBLY SOME OTHER WHERE CLAUSES
    CASE
        WHEN @SECOND IS NULL THEN 
            X = @FIRST
        ELSE 
            X >= @FIRST AND X <= @SECOND 
    END
        -- POSSIBLY SOME MORE WHERE CLAUSES
ORDER BY X

REALLY this feels like it needs an IF/ELSE rather than a CASE but I was directed towards CASE.....

Oh, this is MS SQL >= 2005

Answer 1

Try conditioning on the nullity of @SECOND instead of using CASE.

SELECT *
FROM BANK_DETAIL
WHERE
-- other conditions go here
AND ((@SECOND IS NULL AND X = @FIRST)
  OR (@SECOND IS NOT NULL AND X >= @FIRST AND X <= @SECOND))

Answer 2

SELECT * FROM BANK_DETAIL 
WHERE
    (@SECOND IS NULL AND X = @FIRST) or
    (X >= @FIRST AND X <= @SECOND)

although it would be more efficient to do:

IF @SECOND IS NULL
BEGIN
   SELECT * FROM BANK_DETAIL 
   WHERE
     X = @FIRST
END
ELSE
BEGIN
   SELECT * FROM BANK_DETAIL 
   WHERE
     X >= @FIRST AND X <= @SECOND
END