MySQL Stored Procedure

Stored procedures are an unnecessary evil as far as I can tell but, its worth while to know how to do them right. In this example there will be a review of MySQL stored procedure syntax and technique and an introduction to MySQL's automatic variable.

Save the following stored procedure script in a text file.

drop procedure if exists dorepeat;
DELIMITER //
CREATE PROCEDURE dorepeat(IN p1 INT, OUT x INT)
BEGIN
  SET x = 0;
  REPEAT 
      SET x = x + 1; 
      UNTIL x > p1 
  END REPEAT;
END
//
DELIMITER ;

Save the code above as dorepeat.sql

Create the stored procedure as follows:

$ mysql -D test -u me -p <dorepeat.sql
Enter password: ******

The -D switch connects to the test database. The less than sign is used to import the text file which contains the stored procedure script. After the script runs, you are returned to the prompt.

Here, interactively connect to the database and execute the stored procedure using '@x' an automatic variable.

$ mysql -u me -p 
Enter password: ******
mysql> connect test;

mysql> call dorepeat(4, @x);
Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
+-------+
|  @x   |
+-------+
|   5   |
+-------+
1 row in set (0.00 sec)