MySQL Stored Procedures

What is a Stored Procedure?

A stored procedure is a precompiled SQL code that can be saved and reused.

If you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

A stored procedure can also have parameters, so it can act based on the parameter value(s) that is passed.

Key Benefits of Stored Procedures

Stored procedures are widely used in database management, and have the following benefits:

• Code Reusability - The same procedure can be called from various applications
• Improved Performance - Stored procedures are precompiled and runs faster
• Database Security - You can set users permission to run a specific procedure (limits direct access to tables)
• Easy Maintenance - When updating a procedure, it automatically updates all its use

Stored Procedure Syntax

DELIMITER //

CREATE PROCEDURE procedure_name
  @param1 datatype,
  @param2 datatype
BEGIN
  -- SQL_statements to be executed
  SELECT column1, column2
  FROM table_name
  WHERE columnN = @paramN;

END //

DELIMITER;

Execute a Stored Procedure

To run a stored procedure, use the CALL statement:

CALL procedure_name('value1', 'value2');

Drop a Stored Procedure

To delete a stored procedure, use the DROP PROCEDURE statement:

DROP PROCEDURE procedure_name;

Tip: To ensure that DROP PROCEDURE does not return an error, if the procedure is missing, add the IF EXISTS clause:

DROP PROCEDURE IF EXISTS procedure_name;

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table in the Northwind sample database:

Stored Procedure Example

The following SQL creates a stored procedure named "GetCustomersByCity" that can be used to select Customers from a particular City in the "Customers" table:

Example

DELIMITER //

CREATE PROCEDURE GetCustomersByCity
  @City VARCHAR(50)
BEGIN
  SELECT * FROM Customers
  WHERE City = @City;
END //

DELIMITER;

Here we execute the stored procedure by passing a city ('London') as a parameter, and the stored procedure returns the relevant details from the "Customers" table:

Example

CALL GetCustomersByCity('London');

Stored Procedure With Multiple Parameters

Adding multiple parameters is easy. Just list each parameter and the data type separated by a comma as shown below.

The following SQL creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the "Customers" table:

Example

DELIMITER //

CREATE PROCEDURE GetCustomersByCity
  @City
VARCHAR(50),
  @PostalCode VARCHAR(10)
BEGIN
  SELECT * FROM
Customers
  WHERE City = @City AND PostalCode = @PostalCode;
END //

DELIMITER;

Execute the stored procedure above as follows:

Example

CALL GetCustomersByCity('London', 'WA1 1DP');