How to Create a Stored Procedure in MySQL

08/06/2021

Contents

In this article, you will learn how to create a stored procedure in MySQL.

Creating a stored procedure in MySQL

Creating a stored procedure in MySQL can be a useful way to encapsulate a set of SQL statements that can be reused multiple times. Here are the steps to create a stored procedure in MySQL:

  • Start by logging in to the MySQL server using a privileged account that has permission to create procedures.

  • Once you are logged in, switch to the database where you want to create the procedure using the following command:
    USE database_name;
    Replace database_name with the name of the database where you want to create the procedure.

  • Create the stored procedure using the CREATE PROCEDURE statement, specifying the procedure name, input parameters (if any), and the SQL statements that will be executed when the procedure is called. Here’s the basic syntax:

    CREATE PROCEDURE procedure_name (input_parameter1 datatype1, input_parameter2 datatype2)
BEGIN
    -- SQL statements
END;

    Replace procedure_name, input_parameter1, datatype1, input_parameter2, datatype2, and SQL statements with your own values.

  • Once the procedure is created, you can call it using the CALL statement followed by the procedure name and any input parameters. Here’s the basic syntax:

    CALL procedure_name(input_value1, input_value2);

    Replace procedure_name, input_value1, and input_value2 with your own values.

Here’s an example of creating a simple stored procedure in MySQL:

USE my_database;

CREATE PROCEDURE get_customer_count (OUT count INT)
BEGIN
    SELECT COUNT(*) INTO count FROM customers;
END;

This procedure selects the number of customers from the customers table and stores the count in the count output parameter. Here’s how you can call the procedure:

CALL get_customer_count(@count);
SELECT @count;

This will execute the get_customer_count procedure and store the count in the @count variable. The second statement selects the value of the variable and displays it in the result set.

#MySQL