Monday, August 16, 2010

MySQL (No data - zero rows fetched, selected, or processed)

Trying execute a sp in unit test, the tester stop because a one warning (No data - zero rows fetched, selected, or processed), the sp is rigth, but when try run return a error that we will see below.

Ex:

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_test` $$
CREATE PROCEDURE `sp_test`()
BEGIN
DECLARE v_employee_id INT;
SELECT id INTO v_employee_id
FROM employee
WHERE name = 'Ozahata';
SELECT CONCAT('The id is ', v_employee_id);
END $$
DELIMITER ;

execute this and calling (call sp_test();) will return:
+-------------------------------------+
| CONCAT('The id is ', v_employee_id) |
+-------------------------------------+
| NULL |
+-------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

See the warning: (show warnings;)
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

This happend because not found a information in the select and try put into a variable.

To fix you need execute like the sp below.

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_test` $$
CREATE PROCEDURE `sp_test`()
BEGIN
DECLARE v_employee_id INT;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_employee_id=0;
SELECT id INTO v_employee_id
FROM employee
WHERE name = 'Ozahata';
SELECT CONCAT('The id is ', v_employee_id);
END $$
DELIMITER ;

will have the result:

+-------------------------------------+
| CONCAT('The id is ', v_employee_id) |
+-------------------------------------+
| The id is 0 |
+-------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Back to work. ;)

0 comments: