I was doing some routine code review this past week when I came across something I wasn’t familiar with. A very cryptic looking chunk of SQL. It turns out the code I was looking at was a Stored Procedure.

What is a Stored Procedure? A Stored Procedure is a set of SQL statements with an assigned name that’s stored in the database in compiled form so that it can be shared by a number of programs. This concept should be familiar, if you have been developing for any bit of time: method definition and execution. Sounds like a great way to DRY up some SQL queries or aggregations.

When would you want to use a Stored Procedure? You can leverage the power of these procedures when you want to move intensive work to the DB. One example may be when you want to create a lot of seed data for your application. You can use a Stored Procedure to create, update, and aggregate your data at the database level.

The structure of a procedure can contain…

  • SQL Statements

  • Variable Definitions

  • Conditional Statements

  • Loops

  • Handlers

It is recommended to surround your procedure with BEGIN and END to improve readability, however a multi line statement will require the use of BEGIN and END. Quick tip, changing the SQL delimiter to something other than a ; will allow you to use a semicolon in your method. Let’s look at an example.

  DELIMITER $$
  CREATE PROCEDURE country_hos
  (IN con CHAR(20))
  BEGIN
    SELECT Name, HeadOfState FROM Country
    WHERE Continent = con;
  END $$
  DELIMITER ;

First see how we use a ; in the where clause. This is allowed because we have changed the delimiter to a $$. Notice how we END our statement to close the above definition and swap the delimiter back to ;.

And call your procedure.

  CALL country_hos('Europe');

Lets look at some more demo examples.

First lets create a database. Inside your terminal open a mysql instance and enter

  // in mysql prompt

  CREATE DATABASE my_db; 

  USE my_db;

  DROP TABLE IF EXISTS person;

  CREATE TABLE person(
    person_id SMALLINT UNSIGNED NOT NULL,
    first_name VARCHAR(45) NOT NULL,
    last_name VARCHAR(45) NOT NULL,
    PRIMARY KEY (person_id)
  ) ENGINE=InnoDB;

  INSERT INTO person (person_id, first_name, last_name)
  VALUES
  (1, 'Harry', 'Potter'),
  (2, 'Severus', 'Snape'),
  (3, 'Hermoine', 'Granger'),
  (4, 'Albus', 'Dumbledore'),
  (5, 'Ron', 'Weasley');

So we created a db named my_db, added a person table, and filled that person table with 5 Harry Potter Pals. Now lets create our very first procedure.

  // in mysql prompt

  DELIMITER $$

  CREATE PROCEDURE count_potter_pals()
  BEGIN
    SELECT COUNT(person_id) FROM person;
  END $$

  DELIMITER ;
  CALL count_potter_pals; 
  // we do not need `()` when calling `count_potter_pals();` 
  // because we have no IN or OUT parameters

If you ever want to check your stored procedure, in a mysql prompt do the following.

  // to see the defined procedure
  SHOW CREATE PROCEDURE count_potter_pals\G

  // or to see general info on your procedure
  SHOW PROCEDURE STATUS LIKE '%potter%'\G

Stored Procedures can also take parameters. These can be IN, OUT or INOUT.

IN are the inputs to the procedure.

OUT are the outputs from the procedure.

INOUT are both input and outputs for the procedure.

Lets look at some examples.

  // IN
  DELIMITER $$

  CREATE PROCEDURE get_potter(IN first VARCHAR(45))
  BEGIN
    SELECT * FROM  person
    WHERE first_name = first
    AND last_name = 'Potter';
  END$$

  DELIMITER;

  CALL get_potter('Harry');
  // out
  DELIMITER $$
  CREATE PROCEDURE get_harry(IN first VARCHAR(45), OUT f_name VARCHAR(45))
  BEGIN
    SELECT first_name INTO f_name FROM person
    WHERE first_name = first;
  END $$
  DELIMITER;

  CALL get_harry('Harry', @harry);
  SELECT @harry;
  // returns @harry set to 'Harry'

In the above statement, INTO, sets the second argument passed into the first_name value found. Since the second value passed in is @harry, this variable is set inside the SQL statement. Now we can get the value stored in @harry from running the procedure.

SELECT @harry; // returns @harry set to 'Harry'

Now that we have an understanding of IN and OUT, I will let you try and work through an INOUT example. First set a value in a mysql prompt. SET @val=10. Then use an INOUT procedure and pass @val into the method. After you have created your INOUT procedure, @val should have a new value when you run a select on the variable. SELECT @val;

Now go procedure all the things!