MySQL Stored Procedure tutorial

July 30, 2009

Image for MySQL Stored Procedure tutorial from unsplash.com

How to create and use a stored procedure in MySQL:

mysql > CREATE TABLE team (
id INT(3) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
team_name VARCHAR(20) NOT NULL, 
position TINYINT(3) UNSIGNED NOT NULL) 
ENGINE=INNODB;

INSERT INTO team (team_name, position) 
VALUES ('McLaren',1),('Ferrari',2), ('Brawn',3), ('Toyota',4), 
('Red Bull', 5), ('Torro Rosso', 6), ('Williams', 7), ('Force India',8),
('BMW', 9), ('Renault', 10);

DELIMITER |
CREATE PROCEDURE position_team(teamid INT, newposition INT)
BEGIN
UPDATE team SET position=(position+1) WHERE position=newposition;
UPDATE team SET position=newposition WHERE id=teamid;
SELECT position, team_name FROM team ORDER BY position ASC;
END
|
DELIMITER ;

To call this use the 'call' keyword and pass the parameters:

CALL position_team(2, 1);

+----------+-------------+
| position | team        |
+----------+-------------+
|        1 | Ferrari     |
|        2 | McLaren     |
|        3 | Brawn       |
|        4 | Toyota      |
|        5 | Red Bull    |
|        6 | Torro Rosso |
|        7 | Williams    |
|        8 | Force India |
|        9 | BMW         |
|       10 | Renault     |
+----------+-------------+