The problem is likely the comparison to NULL
, as explained in David Spillett's answer above. When @UserRole = 'Analyst'
, the comparison SupervisorApprovedBy = NULL
will give UNKNOWN
(and the row won't pass the WHERE
test).
You can rewrite with nested CASE
expressions:
WHERE 1 =
CASE
WHEN @UserRole = 'Analyst' THEN
CASE WHEN SupervisorApprovedBy IS NULL THEN 1 END
WHEN SupervisorApprovedBy IS NOT NULL THEN 1
END
Or a complicated CASE
expression:
WHERE 1 =
CASE
WHEN @UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL THEN 1
WHEN @UserRole = 'Analyst' THEN 0
WHEN SupervisorApprovedBy IS NOT NULL THEN 1
END
or with a bit more easy to understand AND
/ OR
:
WHERE
@UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL
OR @UserRole <> 'Analyst' AND SupervisorApprovedBy IS NOT NULL
OR @UserRole IS NULL AND SupervisorApprovedBy IS NOT NULL
Another issue is that SupervisorApprovedBy = SupervisorApprovedBy
(and the equivalent SupervisorApprovedBy IS NOT NULL
I used above) will not give you "all data". The rows where SupervisorApprovedBy
is null will not be returned. If you do want them all, the conditions should be all adjusted:
WHERE 1 =
CASE
WHEN @UserRole = 'Analyst' THEN
CASE WHEN SupervisorApprovedBy IS NULL THEN 1 END
ELSE 1
END
WHERE 1 =
CASE
WHEN @UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL THEN 1
WHEN @UserRole = 'Analyst' THEN 0
ELSE 1
END
WHERE
@UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL
OR @UserRole <> 'Analyst'
OR @UserRole IS NULL