Getting Started with MySQL Stored Procedures

Summary: in this tutorial, you will learn how to write the first MySQL stored procedure using CREATE PROCEDURE statement. We will show you how to call a stored procedure in your SQL script.

Writing the first MySQL stored procedure

We are going to develop a simple stored procedure named GetAllProducts() to help you get familiar with the syntax. The GetAllProducts() stored procedure selects all products from the products table.

First launch the mysql client tool and type the following commands:

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

Let’s examine the stored procedure in greater detail:

• The first command is DELIMITER //, which is not related to the stored procedure syntax. The DELIMITER statement changes the standard delimiter which is semicolon ( ;) to another. In this case, the delimiter is changed from the semicolon( ;) to double-slashes //. Why do we have to change the delimiter? Because we want to pass the  stored procedure to the server as a whole instead of letting mysql tool interprets each statement at a time when we type.  Following the END keyword, we use delimiter // to indicate the end of the stored procedure. The last command ( DELIMITER;) changes the delimiter back to the standard one which is the semicolon ( ;).
• The CREATE PROCEDURE statement is used to create a new stored procedure. You specify the name of stored procedure after the CREATE PROCEDURE statement. In this case, the name of the stored procedure is GetAllProducts. Do not forget the parenthesis after the name of the store procedure or you will get an error message.
  
• The section between BEGIN and END is called body of the stored procedure. You put the declarative SQL code inside the stored procedure’s body to handle business logic. In this stored procedure, we used simple SELECT statement to query data from the products table.

It’s kind of tedious to write the stored procedure in mysql client tool especially when the stored procedure is complex. Most of the GUI tool for MySQL allows you to create new stored procedures via an intuitive interface. For example, in MySQL Workbench, you can create a new stored procedure as follows:

Right mouse click on the Routines and choose “Create Procedure…”

Enter the stored procedure code and click the Apply button

You can review the code before MySQL stores it in the database. Click Apply button if everything is good.

MySQL compiles and puts the stored procedure in the database catalog; click the Finish button.

You can see a new stored procedure created under Routines of the classicmodels database

We have created a new stored procedure. Now it’s time to learn how to use it.

Calling stored procedures

In order to call a stored procedure, you use the following SQL command:

CALL STORED_PROCEDURE_NAME()

You use the CALL statement to call a stored procedure e.g., to call the GetAllProducts stored procedure, you use the following statement:

CALL GetAllProducts();

If you execute the statement above, you will get all products in the products table.

In this tutorial, you have learned how to write a simple stored procedure using the CREATE PROCEDURE statement and call it using the CALL statement.