How to use IF ELSE inside WHERE clause

Question

Is it possible to use IF ELSE inside WHERE clause something like this...

WHERE transactionId like @transactionId
AND transactionType like @transactionType
AND customerId like @customerId
IF(@transactionType='1')
AND qTable.statusId like 'SIGNED'
ELSE
AND rTable.statusId like 'ACTIVE'

possible duplicate of [SQL: IF clause within WHERE clause](http://stackoverflow.com/questions/87821/sql-if-clause-within-where-clause) — Alberto Solano, Apr 11 '14 at 08:03

score 2 · Answer 1 · answered Apr 11 '14 at 08:02

You cannot use the IF ELSE statement within the WHERE clause. Use CASE instead:

WHERE transactionId like @transactionId
AND transactionType like @transactionType
AND customerId like @customerId
AND CASE WHEN @transactionType = '1' THEN qTable.statusId like 'SIGNED' 
    ELSE rTable.statusId like 'ACTIVE' END

Milen · Answer 2 · 2014-04-11T09:03:12.780

1

WHERE transactionId like @transactionId
AND transactionType like @transactionType
AND customerId like @customerId
AND 
  ((@transactionType='1' AND qTable.statusId like 'SIGNED')
OR 
  (@transactionType <> '1' AND like 'ACTIVE') )

edited Apr 11 '14 at 09:03

answered Apr 11 '14 at 07:59

Milen

8,697
7
43
57

score 0 · Answer 3 · answered Apr 11 '14 at 07:57

Is this what you need?

WHERE transactionId like @transactionId
AND transactionType like @transactionType
AND customerId like @customerId
AND ((transactionType='1' and qTable.statusId like 'SIGNED') or (transactionType <> '1' and rTable.statusId like 'ACTIVE'))

How to use IF ELSE inside WHERE clause

3 Answers3