hi guys! im not familiar with mysql can you please help me to solve my problem? I have a stored procedure below that basically search my student table and in my ELSE clause my WHERE clause has CASE statement under it but im not sure if it is posible in mysql, im just taking my chances, but when i try to execute it it gives me an error...If it is posible to have a CASE statement under WHERE clause, can you help me guys to solve this problem? basically, what im trying to do in my WHERE clause under ELSE clause is search the student table based from the searchType variable e.g. if searchType is equals to 'First Name' then search the student table where StudFName column containes value equal to searchArg ...Hope you get my point..Thanks in advance!
Code:
DELIMITER $$
DROP PROCEDURE IF EXISTS `dbsample`.`usp_StudentInfo_SEARCH` $$
CREATE PROCEDURE `dbsample`.`usp_StudentInfo_SEARCH` (searchArg VARCHAR(20), searchType VARCHAR(20))
BEGIN
IF searchType = 'Any' THEN
SELECT *
FROM student
WHERE StudFName LIKE searchArg OR
StudLName LIKE searchArg OR
StudMName LIKE searchArg;
ELSE
SELECT *
FROM student
WHERE
CASE
WHEN searchType = 'First Name' THEN StudFName
WHEN searchType = 'Middle Name' THEN StudMName
WHEN searchType = 'Last Name' THEN StudLName
END CASE;
LIKE searchArg;
END IF;
END $$
DELIMITER ;
dBforums
>
help with CASE statment under WHERE cluase
01-31-08, 04:36
Linear Mode
