MySQL stored function with nested IF… ELSE IF… END IF

March 15, 2023

On my database MySQL 8.0.12 version I have this stored procedure

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_test`(
IN spsID VARCHAR(255),
IN spsName VARCHAR(255))
BEGIN

DECLARE m LONGTEXT;

FLUSH HOSTS;

IF spsID <> "-" THEN 
   SET spsID = spsID; 
   SET m = CONCAT('AND sID = \'',spsID,'\'');
ELSE IF spsName <> "-" THEN 
   SET spsName = spsName;
   SET m = CONCAT('AND sName = \'',spsName,'\'');
END IF;
END IF;

SET @s = CONCAT('SELECT * FROM `tbl_a` t WHERE 1 ',m,';');

SELECT @s;

PREPARE `stmt` FROM @`s`;
SET @`s` := NULL;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;

END $$
DELIMITER ;

Executing the SP sending 2022-0001 for spsID value and foo for spsName value I expected this return

SELECT * FROM `tbl_a` t WHERE 1 AND sID = '2022-0001' AND sName = 'foo';

Instead I have this return

SELECT * FROM `tbl_a` t WHERE 1 AND sID = '2022-0001';

What’s wrong with this SP?

Any help really appreciated…

SELECT * FROM `tbl_a` t WHERE 1 AND sID = '2022-0001' AND sName = 'foo';

>Solution :

Instead of 1 IF-ELSE-ENDIF block you should have 2 different IF-ELSE blocks. Apart from this, You should have concatenated variable m along with CONCAT condition –

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_test`(
IN spsID VARCHAR(255),
IN spsName VARCHAR(255))
BEGIN

DECLARE m LONGTEXT DEFAULT '';

FLUSH HOSTS;

IF spsID <> "-" THEN 
   SET spsID = spsID; 
   SET m = CONCAT(m, 'AND sID = \'', spsID, '\'');
END IF;

IF spsName <> "-" THEN 
   SET spsName = spsName;
   SET m = CONCAT(m, 'AND sName = \'', spsName, '\'');
END IF;

SET @s = CONCAT('SELECT * FROM `tbl_a` t WHERE 1 ',m,';');

SELECT @s;

PREPARE `stmt` FROM @`s`;
SET @`s` := NULL;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;

END $$
DELIMITER ;

Demo.