MySQL CREATE PROCEDURE By Practical Examples

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

MySQL CREATE PROCEDURE statement This

query returns all products in the Products table in the sample database.

SELECT * OF products; Code language: SQL (Structured Query Language) (sql)

The following statement creates a new stored procedure that wraps the query:

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

To execute these statements

:

First, start MySQL Workbench.

Second, create a new SQL tab to execute queries

:

Third, enter the statements in the SQL:

<img src

tab =”https://www.mysqltutorial.org/wp-content/uploads/2019/09/MySQL-CREATE-PROCEDURE-example-step-2.png” alt=”

MySQL CREATE PROCEDURE example step 2″ />

Fouth, execute the declarations. Note that you can select all statements on the SQL tab (or nothing) and click the Run 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 do not see the stored procedure, you can click the Update button next to the title SCHEMAS:

Congratulations! has successfully created the first stored procedure in MySQL

.

Let’s examine the syntax of the stored procedure.

The first and last DELIMITER commands are not part of the stored procedure. The first DELIMITER command changes the default delimiter to // and the last DELIMITER command changes the delimiter back to the default which is semicolon (;).

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

This is the basic syntax of the CREATE PROCEDURE statement:

CREATE PROCEDURE procedure_name(parameter_list) BEGIN statements; END //Code language: SQL (Structured Query Language) (sql)In

this syntax

First, specify the name of the stored procedure

• that you want to create after the CREATE PROCEDURE keywords

.

• Second, specify a comma-separated list of parameters for the stored procedure in parentheses after the procedure name. Note that you will learn how to create stored procedures with parameters in the next tutorials.
• Third, write the code between the BEGIN END block. The previous example has only one simple SELECT statement. After the END keyword, place the delimiter character to end the procedure statement.

Executing a stored procedure

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

stored_procedure_name(argument_list);

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

In this syntax, specify the name of the stored procedure after the CALL keyword. If the stored procedure has parameters, you must pass the arguments in parentheses after the stored procedure name.

This example shows how to call the GetAllProducts() stored procedure:

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

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

Here is the partial result:

<img src

=”https://www.mysqltutorial.org/wp-content/uploads/2019/09/MySQL-CREATE-PROCEDURE-output.png”

alt=”” />

Creating a Stored Procedure Using MySQL Workbench Wizard

When using the MySQL Workbench wizard, you don’t have to deal with many things, such as delimiters or running the command to create stored procedures.

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

The following tab opens

:

<img src

=”https://www.mysqltutorial.org/wp-content/uploads/2019/09/MySQL-Workbench-create-stored-procedure-step-1-1.png”

alt=”” />

Second, rename the stored procedure and add the code between the BEGIN END block:

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

Third, click the Apply button,

MySQL Workbench will open a new window to review the 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:

Summary

Use the

• CREATE PROCEDURE statement to create a new stored procedure. Use the
• CALL statement to execute a stored procedure

.

• MySQL stores stored procedures on the server.