Stored Procedures in MySQL are the typical primer for great ideas and bad implementation. Even that I have stored procedures, because of their performance penalties, recently I had to write one. The procedure itself was very simple, but there were some catchy stages I would like to describe in case I need to write such again.

 

1| What is MySQL Stored Procedure

This is the mysql thing for “programming inside mysql” and yes you could write your own functions which are mostly limited to using SQL-derived statements. The functions could be given parameters, could return parameters, exit codes and so on.

If you need “more” on what are Stored Procedures and why do they exists, I’m not the right guy to tell you, so get your ticket to dev.mysql.com or something more eligible resource

 

2| Creating Stored Procedure

The first step you will encounter is to create the procedure.

Comments in SQL and Stored Procedures could be one of these:

/* ….some commented multilined text …..*/ 

— This is one line comment

 

My test procedure (named new_schedule) looked like this:

( I have added comments describing each  row)

-- Drop the procedure if it already exists 
DROP PROCEDURE IF EXISTS new_schedule;

-- Change the line-end delimiter
DELIMITER $$

-- This is the actual create procedure statement
-- Our procedure accepts 2 arguments 
-- IN means we are able only to read the arguments
-- Every argument has also type defined (char(3) or date)
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_schedule`(IN courseCode char(3), IN startDate date)

BEGIN

SELECT * FROM some_table; 

-- End of procedure definition
END$$
DELIMITER ;

 

 

 

From now on, every code example I give is supposed to be inside stored procedure definition, so I won’t add again and again the procedure definitions

3| Variable declarations inside Stored Procedure

As with most of the programming languages, here you could also declare variables. Stored procedures in mysql have “strict type” declarations, which means that you must also mention the type of the variable you are declaring.

[Important Note] It is very important to make sure your variable declarations are at the beginning of the stored procedure. If you try to declare variable later on in the procedure, mysql will throw you some nasty and hard to debug errors.

[/ Important Note]

 

Some example declarations:

DECLARE integer_var INTEGER DEFAULT 0;
DECLARE my_string varchar(100) DEFAULT "";
DECLARE date_var date DEFAULT NULL;
DECLARE varchar_var  varchar(30) DEFAULT "";

-- Assign value to an already defined variable
SET my_string = "Set the variable to some text"

Whatever variable you plan to use inside the procedure, must be firstly defined in the beginning. Later you could assign it a value.

[Important Note]

If you have any cursor or handler declarations ( which are used for looping), you must always use the following declarations order:

• First must be variable/conditional delcarations
• Second are cursor declarations
• Third are handler declarations

[/ Important Note]

 

4| Using IF statements inside Stored Procedure

The next “cool” thing is you have “Flow control statements” or more precisely:

CASE  , IF/THEN/ELSE and even WHILE and LOOP statements.

I will give example for using the IF/THEN/ELSE and also iterating through a LOOP

DECLARE operator VARCHAR(20);
DECLARE m int; 

SET m=5; 

IF m > 3 THEN
   SET s="bigger";
ELSEIF m < 3 THEN
   SET s="smaller";
ELSE
   SET s="equal";
END IF;

 

5| Iterate over SELECT result in a LOOP inside Stored Procedure

One of the thing you will almost 100% need is doing a LOOP. Usually this is when you want to process all result rows from a given select.

Here is one way to use the internal LOOP keyword inside stored procecdure;

First we will declare CURSOR variable which will give us the chance to iterate over result set by using the keyword FETCH . FETCH returns one value at a time.

 

We will also use HANDLER declaration which is used to determine if we have empty result set returned form the SELECT and so don’t iterate into the loop.

Here is the example:

-- Initialize loop_done variable to 0 
DECLARE loop_done INTEGER DEFAULT 0;

-- Temporary variable for iterating days in it 
DECLARE temp_day  INTEGER ;

-- Declare CURSOR 
-- The following SELECT will select for us 7 rows (each containing a DAY) from the day table
DEClARE select_cursor CURSOR FOR  SELECT date FROM days WHERE week='3';

-- Define CONTINUE HANDLER
-- Whenever our result set END after doing the LAST FETCH, our 
-- continue handler will set loop_done to 1 in order to leave the LOOP
DECLARE CONTINUE HANDLER FOR NOT FOUND SET loop_done = 1;


-- Open the CURSOR 
open select_cursor;

-- Start the LOOP 

select_loop: LOOP 
   FETCH select_cursor INTO temp_day;

   IF loop_done = 1 THEN 
     -- LEAVE is a keyword used to END the loop
     LEAVE select_loop;
   END IF; 

   -- Print the day 
   SELECT "We are processing day:", temp_day;

END LOOP select_loop;

-- Don't forget to close the CURSOR
CLOSE select_cursor;

 

6| Exiting a Stored Procedure and Throwing Error Message

Sometimes you may need to exit a procedure in such a way that it will return usable message to the CALLER. The caller could be you if you manually execute the procedure by using CALL() or another stored procedure.

You can exit the procedure and return message by using the “SIGNAL” keyword.

More information on SIGNALs could be found here:

https://dev.mysql.com/doc/refman/5.5/en/signal.html

 

Here is a simple example I was using in my procedure to return an error:

    IF some_int < 10 THEN 
    SIGNAL SQLSTATE '45001'
        SET MESSAGE_TEXT = 'Some_int is too small and is smaller than 10';
    END IF;