Hello everyone!

We have been investing plenty of personal time and energy for many years to share our knowledge with you all. However, we now need your help to keep this blog running. All you have to do is just click one of the adverts on the site, otherwise it will sadly be taken down due to hosting etc. costs. Thank you.

Procedures cannot be called from the SQL statement like functions. A procedure is invoked using a CALL statement. Query returns are not mandatory but OUT can be used to return results. They can return multiple results unlike functions. Can use the IN, OUT and INOUT parameters but not mandatory.


Dummy table


# Table structure
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`surname` varchar(45) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# Current data
mysql> SELECT * FROM student;
+----+---------+---------+---------------------+
| id | name | surname | timestamp |
+----+---------+---------+---------------------+
| 1 | inanzzz | test | 2015-05-01 21:41:17 |
| 2 | bye | world | 2015-05-01 21:42:35 |
| 3 | blog | entry | 2015-05-02 09:27:58 |
| 4 | another | post | 2015-05-02 09:27:58 |
| 5 | hello | replica | 2015-05-02 09:27:58 |
| 6 | inanzzz | blog | 2015-05-02 09:30:01 |
+----+---------+---------+---------------------+
6 rows in set (0.00 sec)

Stored procedure


Keyword IN in parameter below is not mandatory.


DELIMITER $$

DROP PROCEDURE IF EXISTS `student_list_procedure`$$
CREATE PROCEDURE `student_list_procedure` (IN IN_name VARCHAR(45))

BEGIN
SELECT * FROM `student`
WHERE `name` = IN_name;
END$$

DELIMITER ;

Test


mysql> CALL student_list_procedure('inanzzz');
+----+---------+---------+---------------------+
| id | name | surname | timestamp |
+----+---------+---------+---------------------+
| 1 | inanzzz | test | 2015-05-01 21:41:17 |
| 6 | inanzzz | blog | 2015-05-02 09:30:01 |
+----+---------+---------+---------------------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Stored procedure


Keywords IN and OUT in parameters below are not mandatory.


DELIMITER $$

DROP PROCEDURE IF EXISTS `student_total_procedure`$$
CREATE PROCEDURE `student_total_procedure` (IN IN_name VARCHAR(45), OUT OUT_total INT)

BEGIN
SELECT COUNT(*) INTO OUT_total FROM `student`
WHERE `name` = IN_name;
END$$

DELIMITER ;

Test


mysql> CALL student_total_procedure('inanzzz', @total);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @total;
+--------+
| @total |
+--------+
| 2 |
+--------+
1 row in set (0.00 sec)