What is a stored procedure?

Stored procedures have a lot of similarities with functions and triggers. The main difference is they get called on their own - not as a part of select statement, or an automated process.

The syntax for creating a stored procedure

Looks pretty familiar right?

Just like in our functions and triggers, we set a custom delimiter, use the CREATE command, name the procedure, & define our parameters.

Following that, we use the BEGIN and END statements to create a space where we define the things we want our database to do, and then end the CREATE statement with our custom delimiter, and finish by resetting the delimiter to a semicolon.

So what's new here?

The stuff we do in the "do stuff" part is different from a function - rather than transforming data for the purpose of returning a value, we'll be executing commands, like we did with triggers.

Unlike triggers, however, it won't happen automatically - it will happen when we invoke the procedure with our new command - CALL.

The big thing I want you to take away here is this: if you find yourself saying, "Oh man, am I ever getting bored writing out these commands - you know, inserts, updates, deletes, or even select statements - over and over again; I wish there was a way I could simplify this!", oh boy are you in luck!

How to use procedures with CALL

Oh man, calling procedures is so easy!

CALL {procedureName}({arguments});

Remember that arguments are the values we use for the parameters, a.k.a. the input variables.

Once you run that code as a script (remember - script, not statement), then, whenever you want, you can run this command:

CALL boyThatsEasy;

Remember - these things are stored in our database, meaning we can use them whenever we want! Their intended use is to reduce the amount of code we have to write in the long run.

Parameters for our procedures: IN, OUT, & INOUT variables

Input parameters

Just like functions, stored procedures really shine once you start using input parameters.

DELIMITER //
CREATE PROCEDURE madlibs(
  noun  VARCHAR(255),
  verb  VARCHAR(255)
)
BEGIN
  SELECT CONCAT('I could sure ', verb, ' a ', 
    noun, ' right about now.') 
    AS 'What do you feel like?';
END;
//
DELIMITER ;
-- Now we can play madlibs anytime!
CALL madlibs('poutine', 'eat');

Let's take a moment to recognize that, while using procedures just to run SELECT statements is... fine, we're really not doing anything here that couldn't be done with a VIEW.

What's cool about stored procedures is that we can use them for DML.

Let's look at something a little more advanced. The following procedure uses DML in a stored procedure, and selects a value from a table into a variable.

DROP PROCEDURE IF EXISTS addNewBookAndAuthor;
DELIMITER //
-- Let's prefix our parameters, so that 
-- they're easier to recognize. This is 
-- going to get a bit dense. 
CREATE PROCEDURE addNewBookAndAuthor(
  param_title     VARCHAR(255),
  param_first_name  VARCHAR(255),
  param_last_name   VARCHAR(255)
)
BEGIN
-- Declare two local variables.
  DECLARE var_book_id   INT;
  DECLARE var_author_id INT;
-- Get the value of the highest primary keys 
-- from the authors and books tables, add one, 
-- and then assign them as the values for the 
-- local variables. 
SELECT MAX(author_id) + 1 INTO var_author_id 
  FROM authors;
SELECT MAX(book_id) + 1 INTO var_book_id
  FROM books;
-- Insert into the books table the values of 
-- the maximum book id plus one, and the title 
-- passed in as a parameter. 
  INSERT INTO books (book_id, title)
    VALUES (var_book_id, param_title);
-- Do the same thing for the authors table. 
  INSERT INTO authors (
      author_id,
      first_name,
      last_name 
    ) VALUES (
      var_author_id, 
      param_first_name, 
      param_last_name
    );
-- Connect these entries in 
-- the bridging table. 
  INSERT INTO authorship (book_id, author_id) 
      VALUES (var_book_id, var_author_id);
END;
//
DELIMITER ;
-- Now we can add a new book and author into 
-- the books, authors and authorship tables with 
-- one line of code, and without having to look 
-- up the ids in order to create our entry in 
-- the bridging table. 
CALL addNewBookAndAuthor(
  'My Best Friend''s Exorcism', 
  'Grady', 
  'Hendrix'); 

Is the procedure we just defined a perfect fix? Not especially, as you'd want to use an IF statement to see if the book and author really were both new, and throw an error if they weren't.

Instead, what I want you to see is how powerful stored procedures can be for automating and simplifying your DML, while still maintaining the well-defined relationships and entity separations required by good, normalized database architecture.

Output parameters

Stored procedures don't return a value in the same way that functions return a value - you can't use the RETURNS or RETURN commands in a stored procedure, and you can't call a stored procedure in a SELECT statement.

However, that doesn't mean that our stored procedures are a black hole, where none of the values inside the procedure can ever escape. Obviously, values can be added or altered in tables via the DML statements, but what if we wanted to take a value from inside a stored procedure, and then make it available for use in other statements?

In order to talk about how to pass values from a stored procedure out into the wild, we need to talk about a new kind of variable:

Session variables

So far, when we've talked about variables, whether they're parameters or local variables, they've only been in the context of a function. That is the limit of their "scope".

What is variable scope?

The "scope" of a variable means the context in which a variable is accessible.

If we define a function like this:

DELIMITER //
CREATE FUNCTION what_is_anything_plus_two(
  some_user_input INT
)
RETURNS INT
BEGIN
  RETURN some_user_input + 2;
END//

...we can't access the variable some_user_input by name outside of that function.

-- This won't work:
SELECT some_user_input

The scope of the variable some_user_input is limited to that function. The same goes for stored procedures, and triggers.

However, there is a type of variable called a "user-defined variable", or, as it is sometimes more helpfully known, a "session variable".

These variables are accessible by any functions, procedures, SELECT statements, DML, etc. that are in the same session.

What is a session?

In MySQL, a session is a successful connection to the database (and anything that happens over that connection).

For example, in dBeaver, if you open an SQL editor, write some code, run it, write some more code, and then run that, that's all considered to be part of the same session.

If you open a new SQL editor, that's considered a new session, because dBeaver is creating a new connection. If you wait too long between code execution, you may get disconnected. Anytime you disconnect and then reconnect is consider a new session.

Syntax for session variables

User-defined variables must begin with the @ symbol, otherwise MySQL will think they're system variables (and you don't want to mess with system variables if you don't have to).

SET @v1 = 1;
SET @v2 = 4;
SET @v3 = 10;
-- Selects 50
SELECT (@v1 + @v2) * @v3 AS 'Result';

Session variables do not get declared with the DECLARE command. Try using DECLARE outside of a BEGIN/END block and you'll throw an error.

Session variables instead entirely rely on the SET command, like so:

SET @my_variable = 'It''s mine.';
SET @my_variable = 'No, it''s MINE';
-- Selects 'No, it's MINE';
SELECT @my_variable;

Available data types for session variables

Note that, according to the docs Opens in a new window, we can only store a limited number of data types in our user-defined variables: integers, decimals, floats, strings, and NULL values.

Selecting into multiple variables

You can select multiple values into multiple variables in a single statement. Take a look at this:

SELECT first_name, last_name 
  INTO @fname, @lname 
  FROM authors
  WHERE author_id = 4;
-- Selects Denis  
SELECT @fname;
-- Selects Johnson
SELECT @lname;

Note that I'd get an error if my query returned more than one row.

Hopefully you find session variables handy! But let me get back to why I was showing you how to use session variables in the first place: OUT parameters.

Stored procedures can set the value of a session variable. That's really handy! There are a few steps involved...

1. First, you've got to define one of the parameters in your procedure as an output:

DELIMITER //
CREATE PROCEDURE imAnEcho (
-- Input parameter, defined with 'IN'
  IN thingToEcho  VARCHAR(255),
-- Output parameter, defined with 'OUT'
  OUT theEcho   VARCHAR(255)
)
BEGIN
  -- Do stuff
END//
DELIMITER ;

2. Then you've got to SELECT {a value} INTO {the output parameter}, like so:

DELIMITER //
CREATE PROCEDURE imAnEcho (
  IN thingToEcho  VARCHAR(255),
  OUT theEcho   VARCHAR(255)
)
BEGIN
  SELECT thingToEcho INTO theEcho;
END//
DELIMITER ;

At this point, your session variable has been set, and you can use it as you please:

-- Selects 'repeat after me' 
SELECT @sesh_var_echo;

-- Selects 
-- rpt   |first_name |
-- ------|-----------|
-- repeat|Christopher|
-- repeat|David      |
--
SELECT SUBSTR(@sesh_var_echo,1,6) 
  AS 'rpt', first_name 
FROM authors LIMIT 2;

INOUT parameters

Now I know what you're thinking - what if I've got a value for a session variable already set, and I want to use my procedure to update the existing value of the session variable??