Categories
MySQL

Creating and Calling Stored Procedures

In MySQL, a procedure is a named collection of SQL statements that can be stored and executed on the database server. Procedures are used to encapsulate a set of actions or operations that you want to perform on the database. They are typically used for repetitive tasks, complex operations, or to improve code organization and reusability.

  1. Create Stored Procedures
  2. Execute or Call Stored Procedures
  3. Use Parameters (IN, OUT, and INOUT)
  4. Drop Stored Procedure

To call a stored procedure in MySQL, you typically use the CALL statement. Here’s the basic syntax:

CALL procedure_name([arguments]);

Replace procedure_name with the name of your stored procedure, and provide any required arguments based on the definition of the procedure. Let’s first create a stored procedure to fully understand the concept.

Create a Stored Procedure

To create a stored procedure, you need two things: a code editor to write the code for the stored procedure e.g. Notepad or Sublime Text, and a tool that sends the code to the MySQL/MariaDB server and creates the stored procedure. There are several options for compiling your code, such as:

  • MySQL command-line client:
    You can interact with it directly through your terminal or command prompt.
  • MySQL Workbench:
    It offers a user-friendly interface for creating, editing, and running SQL codes (only supports MySQL).
  • HeidiSQL (or another third-party tool):
    Similar to MySQL Workbench, it supports MariaDB, MySQL, Microsoft SQL, PostgreSQL, and SQLite.

To begin this tutorial, we’ll use the MySQL command-line client, which is a common tool that comes with the MySQL server (and XAMPP) installation. To access the MySQL server on your local machine using port 3306 and the root user, follow these steps:

  1. Launch the command prompt/terminal on your computer
  2. Navigate to the mysql installation directory and then to the bin subdirectory, e.g., D:\xampp\mysql\bin.
  3. Type mysql -u root -p and hit enter.
  4. Enter the password when prompted.
::Connecting to the MySQL command-line client
D:\xampp\mysql\bin>mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.4.24-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB>

To create a procedure in MySQL, you use the CREATE PROCEDURE statement followed by the procedure’s name, parameter list (if any), and the SQL statements that constitute the procedure’s body. Here’s the syntax for creating a stored procedure in MySQL:

CREATE PROCEDURE [IF NOT EXISTS] procedure_name(
   [IN parameter1 data_type,
   OUT parameter2 data_type,
   INOUT parameter3 data type...])
 BEGIN
    -- SQL statements for the stored procedure's logic
 END
  • IF NOT EXISTS (optional) prevents an error from occurring if a procedure with the same name exists.
  • IN (default) parameter passes a value into a procedure.
  • OUT parameter passes a value from the procedure back to the caller.
  • INOUT parameter passes a value into the procedure and any changes made by the procedure pass back to the caller.

Let’s create a simple stored procedure that returns the average product price. Here’s an example of creating a procedure. 

-- Creating a simple procedure
CREATE PROCEDURE itempricing()
BEGIN
 SELECT Avg(price) AS priceaverage FROM products;
END;

The stored procedure is named itempricing and is thus defined with the statement CREATE PROCEDURE itempricing(). This stored procedure has no parameters, but the trailing () is still required. BEGIN and END statements are used to delimit the stored procedure body, and the body itself is just a simple SELECT statement.

MySQL Command-line Client Delimiters

If you are using the mysql command-line utility, pay careful attention to this section.

The default MySQL statement delimiter is ; (as you have seen in all of the MySQL statements used thus far). However, the mysql command-line utility also uses ; as a delimiter. If the command-line utility were to interpret the ; characters inside of the stored procedure itself, those would not end up becoming part of the stored procedure, and that would make the SQL in the stored procedure syntactically invalid.

The solution is to temporarily change the command-line utility delimiter from ; to //, as shown in the above example:

-- Creating a simple procedure
DELIMITER //

CREATE PROCEDURE itempricing()
BEGIN
 SELECT Avg(price) AS priceaverage FROM products;
END//

DELIMITER ;

The DELIMITER // tells the command-line utility to use // as the new end of statement delimiter, and you will notice that the END that closes the stored procedure is defined as END // instead of the expected END;. This way the ; within the stored procedure body remains intact and is correctly passed to the database engine. And then, to restore things back to how they were initially, the statement closes with a DELIMITER ;.

Any character can be used as the delimiter except for the \ character.

Executing or Calling Stored Procedures

MySQL refers to stored procedure execution as calling, and so the MySQL statement to execute a stored procedure is simply CALL. CALL takes the name of the stored procedure and any parameters that need to be passed to it. So how would you use this stored procedure? Like this CALL itempricing();:

CALL itempricing();

+--------------+
| priceaverage |
+--------------+
|    18.75     |
+--------------+

CALL itempricing(); executes the just-created stored procedure and displays the returned result. As a stored procedure is actually a type of function, () characters are required after the stored procedure name (even when no parameters are being passed).

Using Parameters

itempricing is a really simple stored procedure it simply displays the results of a SELECT statement. Typically stored procedures do not display results; rather, they return them into variables that you specify.

Here is an updated version of itempricing (you’ll not be able to create the stored procedure again if you did not previously drop it):

DELIMITER //
CREATE PROCEDURE itempricing(
   OUT pl DECIMAL(8,2),
   OUT ph DECIMAL(8,2),
   OUT pa DECIMAL(8,2)
)
BEGIN
   SELECT Min(price) INTO pl FROM products;
   SELECT Max(price) INTO ph FROM products;
   SELECT Avg(price) INTO pa FROM products;
END//
DELIMITER;

This stored procedure accepts three parameters:

  • pl to store the lowest product price,
  • ph to store the highest product price, and
  • pa to store the average product price (and thus the variable names).

Each parameter must have its type specified; here a decimal value is used. The keyword OUT is used to specify that this parameter is used to send a value out of the stored procedure (back to the caller).

MySQL supports parameters of types:

  • IN (those passed to stored procedures),
  • OUT (those passed from stored procedures, as we’ve used here), and
  • INOUT (those used to pass parameters to and from stored procedures).

The stored procedure code itself is enclosed within BEGIN and END statements as seen before, and a series of SELECT statements are performed to retrieve the values that are then saved into the appropriate variables (by specifying the INTO keyword).

Parameter Datatypes

The datatypes allowed in stored procedure parameters are the same as those used in tables.

Note that a recordset is not an allowed type, and so multiple rows and columns could not be returned via a parameter. This is why three parameters (and three SELECT statements) are used in the previous example.

To call this updated stored procedure, three variable names must be specified, as seen here (Note: All MySQL variable names must begin with @):

CALL itempricing(@pricelow,
                    @pricehigh,
                    @priceaverage);

As the stored procedure expects three parameters, exactly three parameters must be passed, no more and no less. Therefore, three parameters are passed to this CALL statement. These are the names of the three variables that the stored procedure will store the results in.

When called, this statement does not actually display any data. Rather, it returns variables that can then be displayed (or used in other processing).

To display the retrieved average product price you could do the following:

CALL itempricing(@pricelow, @pricehigh, @priceaverage);

SELECT @priceaverage;

+---------------+
| @priceaverage |
+---------------+
| 16.133571428  |
+---------------+

To obtain all three values, you can use the following:

CALL itempricing(@pricelow, @pricehigh, @priceaverage);

SELECT @pricehigh, @pricelow, @priceaverage;

+------------+-----------+---------------+
| @pricehigh | @pricelow | @priceaverage |
+------------+-----------+---------------+
| 55.00      | 2.50      | 16.133571428  |
+------------+-----------+---------------+

Using both IN and OUT parameters

Here is another example, this time using both IN and OUT parameters. ordertotal accepts an order number and returns the total for that order:

DELIMITER //
CREATE PROCEDURE ordertotal(
   IN onumber INT,
   OUT ototal DECIMAL(8,2)
)
BEGIN
   SELECT Sum(item_price*quantity)
   FROM orderitems
   WHERE order_num = onumber
   INTO ototal;
END//
DELIMITER ;

onumber is defined as IN because the order number is passed in to the stored procedure. ototal is defined as OUT because the total is to be returned from the stored procedure. The SELECT statement used both of these parameters, the WHERE clause uses onumber to select the right rows, and INTO uses ototal to store the calculated total.

To invoke this new stored procedure you can use the following:

CALL ordertotal(25, @total);

Two parameters must be passed to ordertotal; the first is the order number and the second is the name of the variable that will contain the calculated total.

To display the total you can then do the following:

SELECT @total;

+--------+
| @total |
+--------+
| 160.50 |
+--------+

@total has already been populated by the CALL statement to ordertotal, and SELECT displays the value it contains.

To obtain a display for the total of another order, you would need to call the stored procedure again, and then redisplay the variable:

CALL ordertotal(29, @total);
SELECT @total;

+--------+
| @total |
+--------+
| 180.00 |
+--------+

Using INOUT parameter

In MySQL, INOUT parameters allow you to pass a value into a stored procedure and return a modified value after the procedure execution.

Let’s say you want to create a stored procedure that increments a given number by a specified value and returns the updated result.

-- Create a stored procedure with an INOUT parameter
DELIMITER //
CREATE PROCEDURE IncrementNumber(
   INOUT num INT,
   IN increment_value INT)
BEGIN
    SET num = num + increment_value;
END //
DELIMITER ;

Calling the stored procedure with the INOUT parameter: 

SET @input_number = 10;
SET @increment_value = 5;

-- Call the stored procedure with INOUT parameter
CALL IncrementNumber(@input_number, @increment_value);

-- The @input_number variable now holds the updated value
SELECT @input_number;

This example demonstrates how to create and use an INOUT parameter in a MySQL stored procedure to modify a variable’s value and return the updated result.

Dropping Stored Procedures

After stored procedures are created, they remain on the server, ready for use, until dropped. The drop command removes the stored procedure from the server.

To remove the stored procedure we just created, use the following statement:

DROP PROCEDURE itempricing;

This removes the just-created stored procedure. Notice that the trailing () is not used; here just the stored procedure name is specified.

The DROP PROCEDURE will throw an error if the named procedure does not actually exist. To delete a procedure if it exists (and not throw an error if it does not), use:

 DROP PROCEDURE IF EXISTS