How to Return Multiple Values from CASE clause in Where Condition

Question

I have a condition in which I need to use multiple parameters in the where clause using the conditional operator. I have written the following query and in this condition, I cannot use in clause. Kindly guide me with how can I return multiple parameters from case clause.

select *
from CARDIMPORT
where STATUS =   CASE
WHEN $P{status} = ''
THEN 'E'
ELSE $P{status}
END

When the STATUS is null, I want to show the records of from all the status, that are  'E', 'I', 'A'

Answer 1

You should try following,

Declare @status nvarchar(50) = 'XXXX'
select 
    * 
from cardimport
where
1 = case when isnull(@status,'') = '' then 1 
    else
        case when status = @status then 1 else 0 end
    end 

This will give you all the rows when the status is null and when the status is not null then it will give you only matching data. 

If you want to learn more about SQL, Check out this SQL Certification by Intellipaat.