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

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.

CodePudding user response：

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