How to avoid recursive call in stored procedure?

Question

I am using MySql stored procedure to do operation with my database. Here is sample store procedure.

CREATE DEFINER=`ntadmin`@`%` PROCEDURE `usp_user`(
    IN  cMode       VARCHAR(20),
    IN  nUserID     MEDIUMINT UNSIGNED,
    IN  cEmail      VARCHAR(50),
    IN  cFirstName  VARCHAR(20),
    IN  cLastName   VARCHAR(20),
    IN  nIsMale     TINYINT(1) UNSIGNED
)   DETERMINISTIC
BEGIN
    IF (cMode = "insert") THEN
        IF NOT EXISTS(SELECT 1 FROM user WHERE email = cEmail) THEN
            INSERT INTO user(email, firstname, lastname, ismale)
            VALUES (cEmail, cFirstName, cLastName, nIsMale);

            SET nUserID = LAST_INSERT_ID(); 

            -- Here I would like to send all details of inserted user.
            SET max_sp_recursion_depth = 1;
            CALL usp_user("select", nUserID, null, null, null, null); 
        ELSE
            SELECT -1 AS "UnSuccess", "Email already exists" AS "Error"; ;        
        END IF;  
    ELSEIF (cMode = "select") THEN
        SELECT email, firstname, lastname, ismale
        FROM user 
        WHERE userid = nUserID;
    ELSEIF (cMode = "delete") THEN
        //delete code
        SELECT 1 AS "Success";
    END IF;
END

As you look carefully my code, I am calling this stored procedure recursively in insert using max_sp_recursion_depth. Is there any better option to avoid recursive call?

Thanks.

Should I use Label or GOTO statements?

wchiquito · Accepted Answer · 2016-03-09 13:52:30Z

One option to avoid recursion is changing the structure as follows:

mysql> DELIMITER //

mysql> DROP FUNCTION IF EXISTS `existsEmail`//
Query OK, 0 rows affected (0.00 sec)

mysql> DROP PROCEDURE IF EXISTS `usp_user_delete`//
Query OK, 0 rows affected (0.00 sec)

mysql> DROP PROCEDURE IF EXISTS `usp_user_insert`//
Query OK, 0 rows affected (0.00 sec)

mysql> DROP PROCEDURE IF EXISTS `usp_user_select`//
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS `user`//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `user` (
    ->   `nuserid` MEDIUMINT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `firstname` VARCHAR(20) DEFAULT NULL,
    ->   `lastname` VARCHAR(20) DEFAULT NULL,
    ->   `email` VARCHAR(50) DEFAULT NULL,
    ->   `ismale` TINYINT(1) UNSIGNED DEFAULT NULL,
    ->   PRIMARY KEY (`nuserid`),
    ->   UNIQUE KEY `idx_email` (`email`)
    -> )//
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE `usp_user_insert`(
    ->     IN  `cEmail` VARCHAR(50),
    ->     IN  `cFirstName` VARCHAR(20),
    ->     IN  `cLastName` VARCHAR(20),
    ->     IN  `nIsMale` TINYINT(1) UNSIGNED
    -> )
    -> BEGIN
    ->     DECLARE `_existsEmail` CONDITION FOR SQLSTATE '45000';
    ->     IF (SELECT NOT `existsEmail`(`cEmail`)) THEN
    ->         INSERT INTO `user` (
    ->         `email`,
    ->         `firstname`,
    ->         `lastname`,
    ->         `ismale`
    ->         ) VALUES (
    ->         `cEmail`,
    ->         `cFirstName`,
    ->         `cLastName`,
    ->         `nIsMale`
    ->         );
    ->         CALL `usp_user_select`(LAST_INSERT_ID());
    ->     ELSE
    ->         -- SELECT -1 'UnSuccess', 'Email already exists' 'Error';
    ->         SIGNAL `_existsEmail`
    ->             SET MESSAGE_TEXT = 'Email already exists', MYSQL_ERRNO = 4000;
    ->     END IF;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `usp_user_select`(
    ->     IN `cnuserid` MEDIUMINT UNSIGNED
    -> )
    -> BEGIN
    ->     SELECT
    ->         `email`,
    ->         `firstname`,
    ->         `lastname`,
    ->         `ismale`
    ->     FROM
    ->         `user`
    ->     WHERE
    ->         `nuserid`= `cnuserid`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `usp_user_delete`()
    -> BEGIN
    ->     -- delete code
    ->     SELECT 1 'Success';
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION `existsEmail`(
    ->     `cemail` VARCHAR(50)
    -> ) RETURNS TINYINT(1)
    -> BEGIN
    ->     RETURN
    ->         EXISTS(
    ->             SELECT
    ->                 NULL
    ->             FROM
    ->                 `user`
    ->             WHERE
    ->                 `email` = `cemail`
    ->         );
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> CALL `usp_user_insert`(
    ->     '[email protected]',
    ->     'firstname_user',
    ->     'lastname_user',
    ->     1
    -> )//
+------------------+----------------+---------------+--------+
| email            | firstname      | lastname      | ismale |
+------------------+----------------+---------------+--------+
| [email protected] | firstname_user | lastname_user |      1 |
+------------------+----------------+---------------+--------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> CALL `usp_user_insert`(
    ->     '[email protected]',
    ->     'firstname_user',
    ->     'lastname_user',
    ->     1
    -> )//
ERROR 4000 (45000): Email already exists

Collectives™ on Stack Overflow

How to avoid recursive call in stored procedure?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related