CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlstored-procedures
Jon H
Jon H

Reputation: 1071

Switch case/If in a where clause

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.

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

Upvotes: 4

Views: 3505

Answers (5)

Remus Rusanu
Remus Rusanu

Reputation: 294407

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.

Upvotes: 1

Tom H
Tom H

Reputation: 47392

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.

Upvotes: 0

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174427

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

Upvotes: 2

dmc
dmc

Reputation: 2684

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

Upvotes: 4

Steve Mayne
Steve Mayne

Reputation: 22858

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

Upvotes: 3

Related Questions