英文:
MySQL stored function with nested IF... ELSE IF... END IF
问题
在我的MySQL 8.0.12版本数据库中，我有这个存储过程：
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 ;
执行这个存储过程时，发送 spsID 值为 2022-0001 和 spsName 值为 foo，我期望得到这个返回结果：
SELECT * FROM `tbl_a` t WHERE 1 AND sID = '2022-0001' AND sName = 'foo';
但实际上得到了这个返回结果：
SELECT * FROM `tbl_a` t WHERE 1 AND sID = '2022-0001';
这个存储过程有什么问题？
非常感谢任何帮助...
英文:
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';
答案1
得分: 2
不要在代码部分做翻译，以下是翻译好的内容：
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 -
英文:
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 ;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论