How do i create a stored procedure with parameters in mysql workbench?

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

MySQL CREATE PROCEDURE statement

This query returns all products in the products table from the sample database.

SELECT * FROM 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, launch MySQL Workbench.

Second, create a new SQL tab for executing queries:

Third, enter the statements in the SQL tab:

Fouth, 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 in 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:

Congratulation! you have 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 a 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 one which is semicolon [;].

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

Here 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 list of comma-separated parameters for the stored procedure in parentheses after the procedure name. Note that you’ll learn how to create stored procedures with parameters in the upcoming tutorials.
• Third, write the code between the BEGIN END block. The above example just has a simple SELECT statement. After the END keyword, you place the delimiter character to end the procedure statement.

Executing a stored procedure

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

CALL stored_procedure_name[argument_list];
Code language: SQL [Structured Query Language] [sql]

In this syntax, you specify the name of the stored procedure after the CALL keyword. If the stored procedure has parameters, you need to pass arguments inside parentheses following the stored procedure name.

This example illustrates 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’s the partial output:

Creating a stored procedure using the 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 on 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 the stored procedures in the server.

Was this tutorial helpful?