I have a table with a field that contains an integer which represents the state of a record. This field "intType" may contain values 0-4.
A parameter in my stored procedure "@intUserType" may contain values 0-3
If @intUserType = 0, I need to select the records where intType = 0 or 3 but if @intUserType = 3, I need to return all records where intType > 1, all other values of @intUserType should return no records
The query I am working with seems a bit forced and I feel like it could be simplified, but I can't seem to wrap my head around it.
This is what I am working with:
SELECT * FROM tblEmployees
WHERE (intType = (CASE WHEN @intUserType = 0 THEN 0 ELSE NULL END)
OR intType = (CASE WHEN @intUserType = 0 THEN 3 ELSE NULL END)
OR intType > (CASE WHEN @intUserType = 3 THEN 1 ELSE NULL END))
Maybe it is as good as it needs to be ... I don't know .. I've only been using SQL regulary for a couple of months and I have not had the time to really study it in depth.
Any suggestions for improvements or should I just leave it alone?
The parenthesis are not necessary in this specific case, as the predicate types are identical. Where you to throw in some AND predicates, then parenthesis would be necessary to define the order of evaluation.
You can use parenthesis for clarity if you'd like, but I'd avoid enclosing the entire WHERE clause in superfluous parenthesis as you did in the first code you posted. Redundant parenthesis actually make code more difficult to decipher.
If it's not practically useful, then it's practically useless.