CODEWITHSUNDEEP
sql
Reynan
Reynan

Reputation: 261

How to use IF ELSE inside WHERE clause

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'

Upvotes: 0

Views: 1411

Answers (3)

Milen
Milen

Reputation: 8867

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

Upvotes: 1

Alberto Solano
Alberto Solano

Reputation: 8227

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

Upvotes: 2

Esko
Esko

Reputation: 4207

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

Upvotes: 0

Related Questions