Mysql function with variable query operators

Question

I am creating a function in mysql(8.0.18), with 2 parameters (_id & _operator). Where _id is the record id which needs to be handled and _operator is the action to be performed.

This code is working fine. But, it's quite large and repeated:

DELIMITER $$

DROP FUNCTION IF EXISTS `deleteSubscriber` $$

CREATE FUNCTION `deleteSubscriber`(_id INTEGER, _operator VARCHAR(2)) RETURNS CHAR
    BEGIN
        IF (_operator = '=' OR ISNULL(_operator) = TRUE) THEN
            DELETE FROM `subscribers` WHERE `id` = _id;
            DELETE FROM `subscriber_packages` WHERE `id` = _id;
            ...
        ELSEIF (_operator = '>=') THEN
            DELETE FROM `subscribers` WHERE `id` >= _id;
            DELETE FROM `subscriber_packages` WHERE `id` >= _id;
            ...
        ELSEIF (_operator = '<=') THEN
            DELETE FROM `subscribers` WHERE `id` <= _id;
            DELETE FROM `subscriber_packages` WHERE `id` <= _id;
            ...
        ELSEIF (_operator = '<>' OR _operator = '!=') THEN
            DELETE FROM `subscribers` WHERE `id` != _id;
            DELETE FROM `subscriber_packages` WHERE `id` != _id;
            ...
        END IF;

        RETURN NULL;
    END$$
DELIMITER ;

Here is the code which I am trying. But, getting error:

DELIMITER $$

DROP FUNCTION IF EXISTS `deleteSubscriber` $$

CREATE FUNCTION `deleteSubscriber`(_id INTEGER, _operator VARCHAR(2)) RETURNS CHAR
    BEGIN
        DELETE FROM `subscribers` WHERE `id` _operator _id;
        DELETE FROM `subscriber_packages` WHERE `id` _operator _id;
        ...

        RETURN NULL;
    END$$
DELIMITER ;

Is there a way to pass the query operator as a value to this function so I can remove the repeated code..?

Dark Knight · Accepted Answer · 2020-10-06 18:12:07Z

1

Use Prepared Statement for the same.

CREATE PROCEDURE `deleteSubscriberData`(IN `_id` INT, IN `_operator` VARCHAR(2)) 
BEGIN
    SET @s = CONCAT('DELETE FROM subscribers WHERE id ', _operator, ' ', _id);
    PREPARE stmt1 FROM @s;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
    
    SET @s = CONCAT('DELETE FROM subscriber_packages WHERE id ', _operator, ' ', _id);
    PREPARE stmt2 FROM @s;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
END

edited Oct 6, 2020 at 18:12

answered Oct 6, 2020 at 18:05

Dark Knight

6,6211 gold badge22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mr.Singh Over a year ago

I got this: Error Code: 1336 Dynamic SQL is not allowed in stored function or trigger

Dark Knight Over a year ago

You can make it procedure instead of function. See my updated answer.

Dark Knight Over a year ago

Make sure procedure doesn't have any return type. so remove that return statement

Mr.Singh Over a year ago

Ok, let me try.

Akina · Accepted Answer · 2020-10-06 18:13:58Z

0

CREATE FUNCTION `deleteSubscriber`(_id INTEGER, _operator VARCHAR(2)) RETURNS CHAR
BEGIN
    DELETE 
    FROM `subscribers` 
    WHERE CASE _operator WHEN '>=' THEN `id` >= _id
                         WHEN '<=' THEN `id` <= _id
                         WHEN '!=' THEN `id` != _id
                         ELSE           `id`  = _id
                         END;

-- the same for another tables

        RETURN NULL;
END

answered Oct 6, 2020 at 18:13

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Comments

Mr.Singh · Accepted Answer · 2020-10-06 19:05:58Z

Thanks to @JitendraYadav, Here is my final code.

DELIMITER $$

DROP PROCEDURE IF EXISTS `deleteSubscriber` $$

CREATE PROCEDURE `deleteSubscriber`(_id INTEGER, _operator VARCHAR(2))
   BEGIN
     # Delete subscribers
     SET @subscribers = CONCAT('DELETE FROM subscribers WHERE id ', _operator, ' ', _id);
     PREPARE deleteSubscribers FROM @subscribers;
     EXECUTE deleteSubscribers;
     DEALLOCATE PREPARE deleteSubscribers;

     # Delete subscriber_packages
     SET @subscriberPackages = CONCAT('DELETE FROM subscriber_packages WHERE subscriber_id ', _operator, ' ', _id);
     PREPARE deleteSubscriberPackages FROM @subscriberPackages;
     EXECUTE deleteSubscriberPackages;
     DEALLOCATE PREPARE deleteSubscriberPackages;

     ...
   END$$
DELIMITER ;

Collectives™ on Stack Overflow

Mysql function with variable query operators

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related