CODEWITHSUNDEEP
sqlselect
RedRocket
RedRocket

Reputation: 1733

Using case clause in SQL

I am struggling with the following logic in sql, let say each of my employee have a employee type such as 'E' = Employee, 'N'= Non-Employee, 'S' = Suspend. In my radio button, I have these three status and also I have an all radio button where user can show all three employee types. To show these corresponding data, I am using a store procedure. I have these code below but somehow it is not working, I manage to show 'E', 'N', and 'S' employee type, but when I choose 'A', it only show the employee type where it is null or empty in the column but what I want is to show all employee type. Below is my code: (What I want is to show all three included empty type when I selected all )

select  
    case when e.emp_type is null or e.emp_type = '' or @emp_type = 'A' 
    Then 'A' 
    else e.emp_type end as emp_type  
FROM t_gc_emp e  
WHERE @emp_type = e.emp_type

Upvotes: 1

Views: 50

Answers (1)

Mureinik
Mureinik

Reputation: 311143

The where clause restricts the rows returned to rows with @emp_type = e.emp_type - in order to return all the rows when @emp_type is passed as 'A' you need to add it to the condition:

SELECT CASE WHEN e.emp_type IS NULL OR 
                 e.emp_type = '' OR
                 @emp_type = 'A' 
            THEN 'A' 
            ELSE e.emp_type END AS emp_type  
FROM   t_gc_emp e  
WHERE  @emp_type = 'A' OR @emp_type = e.emp_type

Or, more elegantly, with an in operator: 

SELECT CASE WHEN e.emp_type IS NULL OR 
                 e.emp_type = '' OR
                 @emp_type = 'A' 
            THEN 'A' 
            ELSE e.emp_type END AS emp_type  
FROM   t_gc_emp e  
WHERE  @emp_type IN ('A', e.emp_type)

Upvotes: 2

Related Questions