Doubling up on WHERE condition

Question

Is it possible to do something like what's happening here in the where statement @Age param?

 @AGE Varchar(8)

 SELECT NAME FROM TABLE WHERE 
 (
 If @AGE='man'
  then (AGE = 'man' or AGE = 'boy')
  else (AGE = 'man')
 )
 AND City IS NULL

@Damien_The_Unbeliever: I don't think it's as simple as that e.g. consider that when `@AGE = 'man'` and `AGE = 'boy'` the OP's search condition is TRUE whereas yours is FALSE. – — onedaywhen, Apr 17 '12 at 08:04
@onedaywhen: Why, it would be True. First comparison yields False, second and third yield True. The conjunction of the last two yields True as well, and the disjunction of False and True is, again, True. — Andriy M, Apr 17 '12 at 09:37
@AndriyM: Not sure what you mean by first, second and third. For the values in the case I gave, `If @AGE='man' then (AGE = 'man' or AGE = 'boy')` would evaluate TRUE and the `ELSE` condition would not be tested. — onedaywhen, Apr 17 '12 at 10:20
@onedaywhen - I believe Andriy was referring to the 3 conditions in my first comment, in the order in which they appear. For `@AGE='man'` and `AGE='boy'`, my expression is `TRUE`, the same as the OPs. — Damien_The_Unbeliever, Apr 17 '12 at 10:40
@onedaywhen: Yes, I was talking about the three comparisons in Damien's suggestion, sorry for not clarifying that before. Also, thanks for the test script! — Andriy M, Apr 17 '12 at 10:42
@Damien_The_Unbeliever: I see it now: You changed 'man' to 'MAN' and I'm using a case sensitive collation -- doesn't everyone? :) — onedaywhen, Apr 17 '12 at 10:50

score 2 · Accepted Answer · edited May 23 '17 at 11:56

Yes, something like this:

SELECT NAME FROM TABLE WHERE 
((@AGE='man'
   AND (AGE = 'man' or AGE = 'boy'))
OR
(@AGE <> 'man'
  AND (AGE = 'man')))
AND City IS NULL

Note the condition @AGE <> 'man'may need to be tweaked depending on whether the @AGE variable can be null.

I answered a very similar question here.

This is a very common technique in a WHERE clause. If you want to apply some "IF" logic in the WHERE clause all you need to do is add the extra condition with an boolean AND to the section where it needs to be applied.

Royi Namir · Answer 2 · 2012-04-17T08:59:35.497

1

one way of doing this is with EXEC and build the String command by If's.

something like this :

declare @t nvarchar(max)
@AGE Varchar(8)
set @t=' SELECT NAME FROM TABLE WHERE '


 If @AGE='man' set @t=@t+'(AGE = ''man'' or AGE = ''boy'')'
  else  set @t=@t+'(AGE = 'man')'

set @t=@t+' AND City IS NULL'

edited Apr 17 '12 at 08:59

answered Apr 17 '12 at 06:57

Royi Namir

144,742
138
468
792

onedaywhen · Answer 3 · 2012-04-17T10:52:32.030

In conjunctive normal form ("a series of ANDs", less dependent on parans):

SELECT * 
  FROM TABLE
 WHERE ( @AGE <> 'man' OR (AGE IN ('man', 'boy') ) )
       AND ( @AGE = 'man' OR AGE = 'man' )
       AND CITY IS NULL;

The idea here is to use the implication rewrite rule:

( IF x THEN y )  is equivalent to  ( NOT ( x ) OR y )

Here's some test code:

WITH T 
     AS
     (
      SELECT * 
        FROM (
              VALUES (1, 'man', NULL), 
                     (2, 'boy', NULL), 
                     (3, 'girl', NULL)
             ) AS T (NAME, AGE, CITY)
     ),
     Params
     AS
     (
      SELECT * 
        FROM (
              VALUES ('man'), 
                     ('boy'), 
                     ('girl')
             ) AS T (p_AGE)
     ), 
     Results
     AS
     (
      SELECT Params.*, T.*, 
             CASE WHEN     ( ( p_AGE <> 'man' OR (AGE IN ('man', 'boy') ) ) AND ( p_AGE = 'man' OR AGE = 'man' ) )  THEN 'TRUE'
                  WHEN NOT ( ( p_AGE <> 'man' OR (AGE IN ('man', 'boy') ) ) AND ( p_AGE = 'man' OR AGE = 'man' ) )  THEN 'FALSE'
                  ELSE 'UNKNOWN' 
             END AS result_onedaywhen, 
             CASE WHEN     ( AGE='man' OR (p_AGE='MAN' and AGE='boy') )  THEN 'TRUE'
                  WHEN NOT ( AGE='man' OR (p_AGE='MAN' and AGE='boy') )  THEN 'FALSE'
                  ELSE 'UNKNOWN' 
             END AS result_Damien_The_Unbeliever
        FROM T, Params
     )
SELECT * 
  FROM Results;

score 0 · Answer 4 · answered Apr 17 '12 at 07:26

0

OR you could use decode functions

answered Apr 17 '12 at 07:26

JRK

182
9

1

Question is tagged `sql-server`. `DECODE` doesn't exist on SQL Server. – Damien_The_Unbeliever Apr 17 '12 at 07:32

Doubling up on WHERE condition

4 Answers4