Stored Procedure with mutliple conditions in CASE -> not working as intended [MYSQL]

I have a stored procedure I’m trying to create in MySQL that takes 5 parameters and returns the result of a query. I’m using CASE/WHEN to differentiate between each query, however, when I run the Stored Procedure: nothing returns.

When I run the SQL query within the stored procedure by itself and provide the values needed: it outputs as normal.

I’m not sure if I’m just using the CASE/WHEN incorrectly, or if there’s something else I’m not aware of.
The code is:

DELIMITER $$

CREATE PROCEDURE testSearch(
    IN Tag1 VARCHAR(10),
    IN Tag2 VARCHAR(10),
    IN Tag3 VARCHAR(10),
    IN TagTerm VARCHAR(10),
    IN SearchArea VARCHAR(50)
)
BEGIN

    DECLARE exampleVar2v INT DEFAULT 0;

    CASE
        WHEN
            Tag1 <> 0
            AND Tag2 = 0
            AND Tag3 = 0
            AND TagTerm = "Tag"
            AND SearchArea = "Responsibility"
        THEN
            SELECT DISTINCT
                R_Name
            FROM
                Responsibility
            WHERE
                R_id
            IN
                (SELECT
                    R_id
                FROM
                    Key_Tag
                WHERE
                    Tag_id
                IN
                    (Tag1))
            ;
        WHEN
            Tag1 <> 0
            AND Tag2 <> 0
            AND Tag3 = 0
            AND TagTerm = "Tag"
            AND SearchArea = "Responsibility"
        THEN
           SELECT DISTINCT
                R_Name
            FROM
                Responsibility
            WHERE
                R_id
            IN
                (SELECT
                    R_id
                FROM
                    Key_Tag
                WHERE
                    Tag_id
                IN
                    (Tag1,Tag2)
                GROUP BY
                    Tag_id
                HAVING
                    COUNT(*) = 2)
            ;
        WHEN
            Tag1 <> 0
            AND Tag2 <> 0
            AND Tag3 <> 0
            AND TagTerm = "Tag"
            AND SearchArea = "Responsibility"
        THEN
           SELECT DISTINCT
                R_Name
            FROM
                Responsibility
            WHERE
                R_id
            IN
                (SELECT
                    R_id
                FROM
                    Key_Tag
                WHERE
                    Tag_id
                IN
                    (Tag1,Tag2,Tag3)
                GROUP BY
                    Tag_id
                HAVING
                    COUNT(*) = 3)
            ;
        ELSE
            set exampleVar2v = 1;
        END CASE;
END $$

EDIT:
The call for this look as follows:

CALL testSearch("T1","T2",0,"Tag","Responsibility");

This would trigger the second CASE above.

I’ve outputted the entire thing for ease. Any help would be appreciated. Thank you.

>Solution :

You are passing strings but testing integers try

DELIMITER $$

CREATE PROCEDURE p( IN Tag1 VARCHAR(10), IN Tag2 VARCHAR(10), IN Tag3 VARCHAR(10), IN TagTerm VARCHAR(10),
    IN SearchArea VARCHAR(50)
)
BEGIN

    DECLARE exampleVar2v INT DEFAULT 0;

    CASE 
        WHEN Tag1 <> '0'  AND Tag2 = '0' AND Tag3 = '0'  AND TagTerm = "Tag"  AND SearchArea = "Responsibility"    THEN
            select 'one';
            /*
               SELECT DISTINCT R_Name
            FROM  Responsibility
            WHERE R_id
            IN  (SELECT
                    R_id
                FROM Key_Tag
                WHERE Tag_id
                IN   (Tag1))
            ;
            */
        WHEN Tag1 <> '0'  AND Tag2 <> '0' AND Tag3 = '0' AND TagTerm = "Tag" AND SearchArea = "Responsibility"
        THEN
              select 'two';
              /*
           SELECT DISTINCT R_Name
            FROM  Responsibility
            WHERE R_id
            IN    (SELECT R_id
                   FROM   Key_Tag
                WHERE Tag_id
                IN  (Tag1,Tag2)
                GROUP BY Tag_id
                HAVING  COUNT(*) = 2)
            ;
            */
        WHEN Tag1 <> '0' AND Tag2 <> '0' AND Tag3 <> '0' AND TagTerm = "Tag"  AND SearchArea = "Responsibility"
        THEN
                select 'three';
           /*
              SELECT DISTINCT R_Name
            FROM  Responsibility
            WHERE R_id
            IN (SELECT  R_id
                FROM   Key_Tag
                WHERE  Tag_id
                IN  (Tag1,Tag2,Tag3)
                GROUP BY Tag_id
                HAVING   COUNT(*) = 3)
            ;
            */
        ELSE
               select 'four';
            set exampleVar2v = 1;
        END CASE;
END $$

delimiter ;

CALL p("T1","T2",0,"Tag","Responsibility");

Note if you want to debug just include selects in the appropriate places as a start.

This version does fall into the second when.

Please review https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

Leave a Reply