Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    74

    Unanswered: help with CASE statment under WHERE cluase

    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 ;

  2. #2
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    maybe :

    WHERE
    CASE
    WHEN searchType = 'First Name' THEN StudFName LIKE searchArg
    WHEN searchType = 'Middle Name' THEN StudMName LIKE searchArg
    WHEN searchType = 'Last Name' THEN StudLName LIKE searchArg
    END CASE;


    ?
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ortho, no, not like that

    daimous, if you're getting an error, perhaps you might let us know what it is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2005
    Posts
    74
    Ooops! Sorry for that..I forgot..
    ERROR:
    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 'WHERE
    CASE
    WHEN searchType = 'First Name' THEN StudFName
    ' at line 14

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    possibly it's barfing on the END CASE

    it should be END only

    and there should be no semicolon after it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2005
    Posts
    74
    possibly it's barfing on the END CASE

    it should be END only
    According to MYSQL Documentation it should be END CASE
    and it has semicolon ;...

    But when i try your suggestion...Amazing! it works!
    DELIMITER $$

    DROP PROCEDURE IF EXISTS `dbsample`.`usp_StudentInfo_SEARCH` $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `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
    ORDER BY StudLName;
    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
    LIKE searchArg;
    END IF;

    END $$

    DELIMITER ;
    Thanks a lot!
    Last edited by daimous; 02-01-08 at 00:36.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's the CASE statement, which is different

    your WHERE clause uses a CASE expression



    from the manual page you linked to:
    The syntax of the CASE statement used inside stored routines differs slightly from that of the SQL CASE expression described in Section 10.3, “Control Flow Functions”. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •