Blog

In this article I will show the how stored procedures work (STORED PROCEDURES) using several examples in SQL.

The examples will be helpful and I will explain them step by step. I will be adding more complex examples as you progress through the tutorial.

In these examples we are going to see sql stored procedures of various types:

1. Basic Stored Procedure.
2. Procedure with input parameters IN.
3. Stored procedure with parameters output OUT.
4. procedure with input and output parameters INOUT.

But before showing the examples I would like to make a small point in the basic syntax of a stored routine (another way of calling stored procedures).

Content

• Structure of a STORED PROCEDURE
• Simple Stored Procedure Example
• STORED PROCEDURE with input parameters IN
• Example of STORED PROCEDURE with output parameters
• STORED PROCEDURES video

Structure of a STORED PROCEDURE

If you’re reading this and you’re not familiar with stored procedures, I’d like to briefly explain the syntax of a stored procedure.

The general structure to create a PROCEDURE is:

1. Use the DELIMITER keyword followed by a combination of characters that you know will not appear inside the PROCEDURE.
2. Create the stored procedure with the command CREATE PROCEDURE followed by the name you want to assign it.
3. BEGIN statement to indicate that the SQL code of the SQL procedure begins.
4. SQL code that we want to be executed when the routine is called.
5. Close the code with the END clause followed by the characters defined with the DECLARE command.

The example syntax would be:

DELIMITER //
CREATE PROCEDURE nombre_procedimiento
BEGIN
  /* CODIGO EN SQL A EJECUTAR */
END //

If the procedure included parameters we would put them after the name and between parentheses. As in any programming language.

DELIMITER //
CREATE PROCEDURE nombre_procedimiento ( TIPOPARAMETRO parametro1 TIPOVALOR, ...)
BEGIN
  /* CODIGO EN SQL A EJECUTAR */
END //

Simple Stored Procedure Example

DELIMITER //
CREATE PROCEDURE total_paises
BEGIN
  SELECT COUNT(*)
  FROM pais
END //

This simple example shows the total number of countries stored in a table country with a simple call. To call it we use the following SQL line:

CALL total_paises();

STORED PROCEDURE with input parameters IN

DELIMITER //
CREATE PROCEDURE total_paises_nombrados_como
(IN palabra CHAR(20))
BEGIN
  SELECT COUNT(*) FROM pais
  WHERE nombre LIKE palabra;
END //

This “stored procedure” is a sql routine that allows to calculate the total number of countries in a table country that match a “word” passed as an input parameter. Note that the word can only have a maximum of 20 characters since the input parameter “word” is defined as CHAR(20).

To call this method you must always remember to pass a text as input parameter, otherwise an error will be produced:

CALL total_paises_nombrados_como('españa');
/* devolvería el resultado de la consulta */

Example of STORED PROCEDURE with output parameters

Now it’s time to look at the SQL calls with OUTPUT parameters.

The definition of this type of parameter in stored procedures is through the word OUT.

In the following example you will see it better:

DELIMITER //CREATE PROCEDURE total_countries(OUT total INTEGER)BEGIN SELECT COUNT

INTO total FROM country;END // This procedure calculates the total number of records in the table country

and inserts them with INTO into the output variable OUT. For call SQL procedure

SET @total = 0;
CALL total_paises( @total );
SELECT @total;

and display the content of the variable, I must first create a variable, and then pass it as a parameter to the procedure:

1. In this way I have:
2. Defined a variable with SET. procedure call
3. with CALL.

Displayed the value of the variable with SELECT SQL.

STORED PROCEDURES video

If you prefer to see a complete explanation on video, with some examples and more theory, you can watch the following video.