Where clause with switch case or dynamic where

Question

What I want to do is based on a condition switch between where clauses conditions.. is this possible?

This is an example, the actual procedure is much longer and I do not want to use if on select.

Is it possible?

declare @city varchar(100) = 'NY'

SELECT s.suppliers, o.order
FROM suppliers s
INNER JOIN orders o ON s.id = o.id
WHERE 
    CASE WHEN @city = 'NY' 
           THEN (s.SupplierName = o.SupplierName AND o.Row = 'New') 
         ELSE s.SupplierName = o.SupplierName 
    END

CASE is an expression with one output, you can not use it for control of flow like an IF condition. http://sqlperformance.com/2014/06/t-sql-queries/dirty-secrets-of-the-case-expression — Aaron Bertrand, Apr 01 '15 at 18:56
@AaronBertrand And you can't use if either, so what would be an alternative? — NoviceDeveloper, Apr 01 '15 at 18:58
`WHERE s.SupplierName = o.SupplierName AND o.Row = CASE @city WHEN 'NY' THEN 'New' ELSE o.Row END`. A little more complicated if o.Row is nullable. — Aaron Bertrand, Apr 01 '15 at 18:59
(Also, is this SQL Server or PostGres? Please only tag with the actual, relevant platform.) — Aaron Bertrand, Apr 01 '15 at 19:00
possible duplicate of [Conditional where clause in Sql Server?](http://stackoverflow.com/questions/18629132/conditional-where-clause-in-sql-server) — Tab Alleman, Apr 01 '15 at 20:01

score 2 · Accepted Answer · answered Apr 01 '15 at 19:02

2

I've done something similar to this before:

WHERE s.SupplierName = o.SupplierName 
AND
CASE WHEN @city = 'NY' AND o.Row = 'New' THEN 1
WHEN @city <> 'NY' THEN 1
ELSE 0 END = 1

You can get as complicated as you want, but watch what you do to keep the performance reasonable.

answered Apr 01 '15 at 19:02

JBrooks

9,901
2
28
32

this gives 1 or 0 .. doesn't quite get the result I want. – NoviceDeveloper Apr 01 '15 at 19:34
Your logic will produce a 1 when true and then the where clause will be true. – JBrooks Apr 01 '15 at 19:39
@NoviceDeveloper - See [this](http://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) for another explanation. – HABO Apr 01 '15 at 19:58

score 1 · Answer 2 · answered Apr 01 '15 at 19:19

1

You don't need a case for this, and you can put the common condition in the ON clause:

SELECT s.suppliers, o.order
FROM suppliers s INNER JOIN 
     orders o
     ON s.id = o.id AND s.SupplierName = o.SupplierName
WHERE (@city = 'NY' AND o.Row = 'New') OR
      (@city <> 'NY');

This assumes that @city is not NULL. The logic is only slightly more complicated to include that possibility (although it seems unnecessary).

answered Apr 01 '15 at 19:19

Gordon Linoff

1,242,037
58
646
786

wouldn't this pull the records for both conditions? I think it does... I want it to grab either when city is NY and Row New and if that is not the case then only when city is NY ... not both. – NoviceDeveloper Apr 01 '15 at 19:33
@NoviceDeveloper . . . This implements the logic that you are trying to express with your `case` expression. The `@city` variable only takes on one value for the execution of the query, so only on of the conditions is used on any given run. – Gordon Linoff Apr 01 '15 at 22:25

Where clause with switch case or dynamic where

2 Answers2