Creating new stored procedures –

Summary: in this tutorial, you will learn step-by-step how to the MySQL CREATE PROCEDURE statement to create new stored procedures.

Introduction to MySQL CREATE PROCEDURE statement

To create a stored procedure, you use the CREATE PROCEDURE statement.

Here’s the basic syntax of the CREATE PROCEDURE statement:

CREATE PROCEDURE sp_name(parameter_list)
BEGIN
   statements;
END;

In this syntax:

• First, define the name of the stored procedure sp_name after the CREATE PROCEDURE keywords.
• Second, specify the parameter list (parameter_list) inside the parentheses followed by the stored procedure’s name. If the stored procedure has no parameters, you can use an empty parentheses ().
• Third, write the stored procedure body that consists of one or more valid SQL statements between the BEGIN and END block.

If you attempt to create a stored procedure that already exists, MySQL will issue an error.

To prevent the error, you can add an additional clause IF NOT EXISTS after the CREATE PROCEDURE keywords:

CREATE PROCEDURE [IF NOT EXISTS] sp_name ([parameter[,...]])
routine_body;

In this case, MySQL will issue a warning if you attempt to create a stored procedure with a name that already exists, instead of throwing an error.

Note that the IF NOT EXISTS clause has been available since MySQL version 8.0.29.

MySQL CREATE PROCEDURE statement example

We’ll use the products table in the sample database for the demonstration:

Notice that we will present only the syntax and steps for defining a new stored procedure. In the upcoming tutorial, you will learn how to define a stored procedure with parameters.

The following statements create a new stored procedure called GetAllProducts():

DELIMITER //

CREATE PROCEDURE GetAllProducts()
BEGIN
	SELECT *  FROM products;
END //

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

How it works:

First, change the default delimiter to //:

DELIMITER //

Second, use the CREATE PROCEDURE statement to create a new stored procedure. Because we have changed the delimiter to //, we can now use the semicolon (;) inside the stored procedure:

CREATE PROCEDURE GetAllProducts()
BEGIN
	SELECT *  FROM products;
END //

Third, change the delimiter back to the default delimiter, which is a semicolon (;):

DELIMITER ;

Creating a stored procedure using MySQL client

First, connect to the classicmodels sample database using the mysql client:

C:\>mysql -u root -p classicmodels
Enter password: ********

Second, change the delimiter to //:

mysql> DELIMITER//Code language: JavaScript (javascript)

Third, type the following code to create the stored procedure:

mysql> CREATE PROCEDURE GetAllProducts()
    -> BEGIN
    -> SELECT * FROM products;
    -> END//
Query OK, 0 rows affected (0.01 sec)Code language: JavaScript (javascript)

Finally, change the delimiter back to a semicolon:

DELIMITER ;

Creating a stored procedure using MySQL workbench

First, launch MySQL Workbench and log in as the root account.

Second, create a new SQL tab for executing queries:

Third, enter the statements in the SQL tab:

Fourth, execute the statements.

Note that you can select all statements in the SQL tab (or nothing) and click the Execute button.

If everything is fine, MySQL will create the stored procedure and save it on the server.

Fifth, check the stored procedure by opening the Stored Procedures node. If you don’t see the stored procedure, you can click the Refresh button next to the SCHEMAS title:

Creating a stored procedure using MySQL Workbench wizard

By using the MySQL Workbench wizard, you don’t have to take care of many things like delimiters or executing the command to create stored procedures.

First, right-click on the Stored Procedures from the Navigator and select the Create Stored Procedure… menu item.

The following tab will open:

Second, change the stored procedure’s name and add the code between the BEGIN...END block:

The stored procedure name is GetAllCustomers() which returns all rows in the customers table from the sample database.

Third, Click the Apply button, MySQL Workbench will open a new window for reviewing SQL script before applying it to the database:

Fourth, Click the Apply button to confirm. MySQL Workbench will create the stored procedure:

Fifth, click the Finish button to close the window.

Finally, view the stored procedure in the Stored Procedures list:

Executing a stored procedure

To execute a stored procedure, you use the CALL statement:

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

In this syntax:

• First, provide the name of the stored procedure that you want to execute after the CALL keyword.
• Second, if the stored procedure has parameters, you need to pass the arguments to it in parentheses () after the stored procedure’s name.

The following illustrates how to execute the GetAllProducts() stored procedure:

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

Executing this statement is the same as running an SQL statement:

Here’s the partial output:

Summary

• Use the CREATE PROCEDURE statement to create a new stored procedure.
• Use the CALL statement to execute a stored procedure.
• MySQL stores the stored procedures in the server.