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

See: http://www.sommarskog.se/dyn-search-2005.html – OMG Ponies May 27 '11 at 14:57 — OMG Ponies, May 27 '11 at 14:57

score 4 · Accepted Answer · answered May 27 '11 at 14:57

4

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))

answered May 27 '11 at 14:57

dmc

2,596
21
24

1

+1 for encapsulating the whole thing in parentheses, but I don't think you need the `IS NOT NULL` – JNK May 27 '11 at 14:59
1

Any comparison to NULL doesn't evaluate to false. It evaluates to NULL. – Tom H May 27 '11 at 15:09
If I was more comfortable with three-valued logic, I'd probably eliminate the `IS NOT NULL` condition. But it just seems clearer to me to leave it in. Maybe a year from now I'll have a different opinion... – dmc May 27 '11 at 15:41

score 3 · Answer 2 · answered May 27 '11 at 14:56

3

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

answered May 27 '11 at 14:56

Steve Mayne

22,285
4
49
49

Depending on caching, I don't think that your second approach will always be more efficient (and also causes the maintenance problem of duplicate code) – Tom H May 27 '11 at 15:07
@Tom: that depends on the planner. I'm not familiar with SQL-Server's internals, but Postgres would prepare the first query in such a way that it does two bitmap index scans (leading to rechecking conditions, and one of which will not necessarily be needed), and it would prepare the next two so that they do a normal index scan. – Denis de Bernardy May 27 '11 at 15:23
Searching within a range is a different prospect to searching for a specific value. Granted it depends on the data in the tables, statistics and the indexing. As a rule of thumb, `OR` clauses aren't typically that great at the top-level of a `WHERE` clause - in my experience. – Steve Mayne May 27 '11 at 15:41

score 2 · Answer 3 · answered May 27 '11 at 14:57

2

I would use boolean operators:

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

answered May 27 '11 at 14:57

Daniel Hilgarth

171,043
40
335
443

score 1 · Answer 4 · answered May 27 '11 at 19:04

You're building a dynamic search condition. By forcing one single statement to cover both cases you are cutting the optimizer options. The generated plan has to work in both cases when @seconds is null and when is not null. You'll be much better using two separate statement:

IF @SECOND IS NULL THEN
   SELECT * FROM BANK_DETAIL 
   WHERE  X = @FIRST
   ORDER BY X
ELSE
   SELECT * FROM BANK_DETAIL 
   WHERE X >= @FIRST AND X <= @SECOND 
   ORDER BY X

You intuition to 'simplify' into one single statement is leading you down the wrong path. The result is less text, but much more execution time due to suboptimal plans. The article linked at the beginning of my response goes into great detail on this topic.

Tom H · Answer 5 · 2011-05-27T15:12:10.797

The problem is that CASE returns a value, it's not a branch in logic. The link that OMG provides is pretty much the authoritative source on this one (almost anything from Erland Sommarskog is going to be great advice).

A quick summary of the link:

You can use dynamic SQL where you build up a statement based on the conditions. This can often be the best performing approach, but there are drawbacks. One of the biggest drawbacks is the possible security issues, so make sure that you fully understand SQL injection attacks and how to prevent them.

Another approach is to use complex logic in your WHERE statement using ORs. In your case it would be something like below. This approach is a bit simpler than dynamic SQL and safer, but performance may not always be great. If performance is ok for your situation though (test it) then stick with this approach.

SELECT
    *    -- I assume that you used * just as an example and don't actually use this in your production code
FROM
    Bank_Detail
WHERE -- POSSIBLY SOME OTHER WHERE CLAUSES
    (@second IS NULL AND X = @first) OR
    (@second IS NOT NULL AND (x >= @first AND x <= @second))
    -- POSSIBLY SOME MORE WHERE CLAUSES
ORDER BY
    x

Another way to organize the statement just occurred to me...

SELECT
    *
FROM
    Bank_Detail
WHERE -- POSSIBLY SOME OTHER WHERE CLAUSES
    x >= @first AND
    x <= COALESCE(@second, @first)
    -- POSSIBLY SOME MORE WHERE CLAUSES
ORDER BY
    x

I haven't tested it yet, but I think that will be logically equivalent and may give you a more consistent query plan.

Erland also gives a couple other possible approaches, so be sure to read his full article on the subject.

While the second version has some appeal, I believe it includes unwanted rows where `x > @first` when `@second is null`. — dmc, May 27 '11 at 15:37
When @second is null, the where clause simplifies to `x >= @first AND x <= @first` - this is equivalent to `x = @first`, as required. — Steve Mayne, May 27 '11 at 15:46

Switch case/If in a where clause

5 Answers5