Q:

Left join results according to parameter value

In my stored procedure I am trying to get the results of a query according to parameter. I have a main table "employee" and table "employee_process" which I make left join with the first one. 
In the results if the parameter's value is 0 I want to get everything from "employee" except the rows which are joined with "employee_process" and if the value is 1 I want to get only the rows which are joined between the two tables. Here is what I have now:
DECLARE @state int = 0

SELECT *
FROM employee e
LEFT JOIN employee_process ep ON ep.employee_id = e.employee_id
--this below is what I want to achieve (but obviously this is not correct)
WHERE CASE WHEN @state = 0 THEN ep.employee_id IS NULL ELSE ep.employee_id IS NOT NULL

How can I achieve that I tried using something like that:
WHERE ( @state ^ ep.employee_id IS NOT NULL )

but with no luck.
Thank you in advance

A:

Rephrase your CASE expression as follows:
SELECT *
FROM employee e
LEFT JOIN employee_process ep
    ON ep.employee_id = e.employee_id
WHERE
    @state = 0 OR ep.employee_id IS NOT NULL;

A CASE expression has to generate actual values, and not other expressions.  But, here we don't even need to use a CASE expression.
Edit:
Based on your comment, it seems we should add another criteria to the WHERE clause:
WHERE
    (@state = 0 AND ep.status <> 'employee_process') OR ep.employee_id IS NOT NULL;

