CODEWITHSUNDEEP
mysqlstored-proceduressyntaxconditional-statements
Mr.Singh
Mr.Singh

Reputation: 1791

MySQL: store procedure query condition operator as parameter

I am creating a Stored Procedure which is to delete the Parent and it's child records located in multiple tables. However, there are only 3 types of queries in want to execute, such as: =, > and >=.

DELIMITER $$

DROP PROCEDURE IF EXISTS `removeStory` $$

CREATE PROCEDURE `removeStory`(IN _id INTEGER, IN _cond VARCHAR(1))
    BEGIN
        # checking condition
        IF ISNULL(_cond) AND (_cond = '=') OR (_cond = '>') OR (_cond = '>=') THEN 
            # removing `comment -> replies`
            DELETE FROM `comment_replies`
            INNER JOIN `comments` ON `comment_replies`.`comment_id` = `comments`.`id`
            WHERE `comments`.`story_id` _cond _id;

            # removing `comments`
            DELETE FROM `comments`
            WHERE `story_id` _cond _id;

            ...

            ...

            # removing `story`
            DELETE FROM `stories`
            WHERE `id` _cond _id;
        END If;
    END$$
DELIMITER ;

CALL removeStory(10, '>=');

I am passing the query operation in the _condition variables, but its giving me the syntax error. Also, is there any way to reduce the the OR conditions.

Upvotes: 0

Views: 52

Answers (1)

Akina
Akina

Reputation: 42728

Compare operator cannot be inserted into the query by such way. You must use either prepared statement or CASE.

...
WHERE CASE _cond WHEN '>'  THEN id >  _id
                 WHEN '<'  THEN id >  _id
                 WHEN '!=' THEN id != _id 
                 ...
                 WHEN '<>' THEN id <> _id 
                 END, 
...

Upvotes: 1

Related Questions