Create MySQL Stored Procedure [With Examples]

In this tutorial, we will learn to create a stored procedure with easy examples. We will start with a basic introduction and move on to the syntax to create a stored procedure. Then we will see multiple examples of stored procedures for effective understanding. Here we go!

Also read: Introduction to MySQL Stored Procedure – A Complete Guide

Introduction and Prerequisites

We can create stored procedures with and without parameters. So, this tutorial will cover examples of parameterized and non-parameterized stored procedures.

If you are new to this topic, we recommend you to read our guide on introduction to stored procedures where we have covered everything that you should know about the topic. Let’s begin with the syntax of stored procedures.

Syntax of MySQL Stored Procedure

We can create a stored procedure using the CREATE PROCEDURE statement. Following is the complete syntax to create a stored procedure-

CREATE PROCEDURE [IF NOT EXISTS] proc_name([parameters])
[characteristics]
procedure_body


parameters:
[ IN | OUT | INOUT ] param_name type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

procedure_body:
    Valid SQL routine statementCode language: SQL (Structured Query Language) (sql)

Where,

• CREATE PROCEDURE – It is a statement to create a stored procedure
• IF NOT EXISTS – It will create a new stored procedure only if the current procedure name doesn’t exist already in the database.
• proc_name – Any name you can give to the procedure.
• parameters – There are three types of parameters – IN, OUT, INOUT. The IN parameter specifies the input value that you are passing to the procedure from the procedure call. The OUT parameter is the value that the procedure will return. On the other hand, the INOUT performs combined work of the IN and OUT parameters.
• Characteristics – You can choose any of the characteristics from the above-mentioned set. Read the official documentation for more information.
• procedure_body – In the body, you write valid SQL statements. You can write a simple statement such as SELECT or INSERT, or you can write multiple statements using a BEGIN END block.

This way you can create a stored procedure. To invoke the stored procedure, the CALL statement is used. Following is the syntax to call the procedure.

CALL proc_name([parameters]);Code language: SQL (Structured Query Language) (sql)

Remember to give the procedure call the correct arguments. You’ll get an error if you specify arguments in the procedure definition but don’t include them in the procedure call.

Examples of MySQL Stored Procedure

We will see four examples here. The first example will be without any parameter. The second and third examples will include IN and OUT parameters respectively. On the other hand, the fourth example will demonstrate the INOUT parameter.

Before that, we will use the following table.

We will also insert some data into the table. Below is the table data that we have inserted.

Make sure you change the delimiter before and after creating a stored procedure.

Create a Stored Procedure Without Parameter

Now, we will create a simple stored procedure that will fetch the table data.

DELIMITER //
CREATE PROCEDURE fetchData()
BEGIN
SELECT * FROM accounts;
END
//
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

We have used a simple statement SELECT to fetch the data from a table, so you may skip the BEGIN END keywords and directly write the SELECT statement.

Let’s test the procedure by calling it.

CALL fetchData();Code language: SQL (Structured Query Language) (sql)

As you can see, we have got the expected output.

Create a Stored Procedure With in Parameter

The IN parameter is used to pass the value from the procedure call to the procedure body. Let’s understand it by the following example.

Let’s create a stored procedure in which we will pass the id of a user and display the data of its account.

DELIMITER //
CREATE PROCEDURE fetch_data_in(IN uid INT)
BEGIN
SELECT * FROM accounts
WHERE id = uid ;
END
//
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

The stored procedure is created successfully. Let’s test it.

CALL fetch_data_in(1);
CALL fetch_data_in(3);Code language: SQL (Structured Query Language) (sql)

As you can see, we have got the account details of entered ids of the customers.

Create a Stored Procedure With OUT Parameter

The OUT parameter is used to output the value from the procedure body. You must specify the OUT parameter while declaring the procedure name. Let’s understand it through the example.

Here, we will create a stored procedure to calculate the total account balance of all customers.

DELIMITER //
CREATE PROCEDURE fetchDataOut(OUT totBalance FLOAT)
BEGIN
SELECT SUM(balance) FROM accounts;
END
//Code language: SQL (Structured Query Language) (sql)

Here, we have used the SUM aggregate function to calculate the total balance. Now let’s call the procedure.

CALL fetchDataOut(@balance);Code language: SQL (Structured Query Language) (sql)

Here, we have used the variable @balance which acts as a session variable and stores the result that the procedure returns.

As you can see, we got the expected output.

Create a Stored Procedure With INOUT Parameter

The INOUT parameter does the work of both IN and OUT parameters in one place. Let’s understand it by the example.

We will create a stored procedure in which we will pass the id of the customer along with the amount to be withdrawn. The amount parameter is INOUT type. So, we will pass the amount from that parameter and later return the account balance from the same variable.

Let’s understand it through the example below.

CREATE PROCEDURE update_data(IN uid INT, INOUT amount FLOAT)
BEGIN
DECLARE uBalance FLOAT;
SELECT balance INTO uBalance FROM accounts
WHERE id=uid;
IF amount > uBalance THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT="Insufficient balance";
ELSE
UPDATE accounts
SET balance= uBalance-amount
WHERE id=uid;
END IF;
SELECT balance into amount from accounts WHERE id= uid;
END //Code language: SQL (Structured Query Language) (sql)

Here, we have checked if the entered amount is greater than the available balance. If it is greater then we will throw an error. If the amount is not greater than the available balance then we will subtract the entered amount from the available balance. After that, we will select the updated balance and copy it into the amount variable which is our INOUT variable.

Let’s test it now.

SET @amount = 100;
CALL update_data(1,@amount);
SELECT @amount;Code language: SQL (Structured Query Language) (sql)

Note that, you can not directly pass the value to the procedure. You must declare the variable first and then pass that variable to the procedure. The same variable will help to pass the parameter to the procedure body as well to get the returned value from the same procedure.

As you can see, after deducting 100 Rs from the account, we are left with 1200 Rs. Let’s check the table data if the same is updated in it.

SELECT * FROM accounts;Code language: SQL (Structured Query Language) (sql)

The data is updated in the table which means our stored procedure works fine.

Let’s try to deduct a greater amount than the available balance and see what result we get.

set @amount = 1300;
CALL update_data(1,@amount);Code language: SQL (Structured Query Language) (sql)

As you can see, we have got an error “Insufficient balance” because our available balance is 1200 Rs and we are trying to withdraw 1300 Rs.

Summary

In this tutorial –

• We learned the syntax of the MySQL stored procedure.
• We learned to create stored procedures using IN, OUT and INOUT parameters.
• We learned how to call stored procedures.