Thread: Using CASE in a Where Clause IN Stored Procedure

(I think) I need to use a CASE statement in a WHERE clause in a Stored Procedure, but I keep getting an error when I try.

I'm writing an SProc to query a users table based on parameters sent to the SP. What I want to be able to do is either select all the variants of a parameter or select a specific one. So, I'm using if statements in my WHERE clause to select whether to use a field or not. This is fine for VARCHAR and INT fields, but I can't seem to come up with a way of doing it for bool fields.

This is what I have so far

Code:

CREATE PROCEDURE `spGetUser`(
id INT,
surname VARCHAR (255),
forename VARCHAR (255),
active CHAR(1),
admin CHAR(1),
culture VARCHAR(10)
)
BEGIN

SELECT
A.UserName,
A.EmailAddress,
A.Forename,
A.Surname,
A.Active,
A.Admin,
(SELECT CONCAT(B.forename, ' ', B.Surname) FROM tblUsers B WHERE B.ID=A.AddedBy) As AddedbyStr,
A.DtAdded,
A.Culture

FROM tblUsers A

WHERE
if(id=0, A.ID=A.ID, A.ID=id) AND
if(surname='', A.Surname=A.Surname, A.Surname=surname) AND
if(forename='',A.forename=A.Forename, A.Forename=forename)
;

END $$

What I want to be able to do is use the "active" to pass "y", "n" or "b" to signify active, non active or both user types. Active in the table is a bool field (TINYINT(1)).

How can I use the parameter "active" in my WHERE clause to select whether to show active, non active or both user types?

Hope that makes sense. I'm using MySQL, btw.

What I want (and what I think should work!) is something like this:-

Code:

if(forename='',A.forename=A.forename, A.forename LIKE forename) AND
A.Active =
CASE
WHEN active='y' THEN true;
WHEN active='n' THEN false;
ELSE A.Active;
END CASE;

ORDER BY A.Surname ASC
;

But it gives a syntax error

Code:

Script line: 4    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
WHEN active='n' THEN false;
ELSE A.Active;
END CASE;

ORDER BY A.Surname A' at line 30

Line 30 is in red.

Any ideas? Have I got the syntax right?

I'm hoping I might have it. I could find no mention of this syntax on the MySQL website. I managed to find a similar example on another site and worked it into mine.

I only have one record in the able at the moment, so difficult to assess if it's right or not, but this is what I have and it's not giving errors.

Code:

if(forename='',A.forename=A.forename, A.forename LIKE forename) AND
A.Active =
CASE
WHEN act='y' THEN true
WHEN act='n' THEN false
ELSE A.Active
END

ORDER BY A.Surname ASC

Note the lack of semi-colons ( ; ) at the end of each line and using END instead of END CASE and no ; at the end of the CASE expression.

<edit>OK, I managed to find it discussed when I searched for CASE expression. The confusion is between different syntaxes used for the CASE statement and the CASE expression...Confusing...See here for reference</edit>