--- title: "An Introduction To Stored Procedures In MySQL | CoderPad" description: "Stored procedures can be extremely useful in improving the efficiency of your database, saving you time, money, and lots of work-related headaches. Many database management systems today can use stored procedures to execute tasks on the database. Learn what a stored procedure is, the pros and how you can use them in yo" canonical: "https://coderpad.io/blog/development/an-introduction-to-stored-procedures-in-mysql/" last_modified: "2026-04-15 16:21:02" --- [Back to blog](https://coderpad.io/blog/) # An Introduction To Stored Procedures In MySQL December 5, 2022 7 Minute Read By Elvis Duru [Development](https://coderpad.io/blog/development/) Many relational database management systems today can use stored procedures to execute tasks on the database. Stored procedures can be extremely useful in improving the efficiency of your database, saving you time, money, and lots of work-related headaches. So let’s dive into exactly how they can help you. ## What is a stored procedure? A stored procedure is a precompiled set of one or more SQL statements stored in a relational database management system that can be reused and shared by multiple programs for the purpose of executing tasks on the database. A stored procedure is usually assigned a name, e.g.`get_all_films`, and it can accept input and output parameters. They share some similarities with functions in procedural programming languages. ## Advantages of stored procedures Stored procedures have some great benefits. When used properly, they help you build powerful database applications and can also offer better performance, higher productivity, ease of use and increased scalability. ### Performance By grouping one or more SQL statements, you can execute the statements in a single call, eliminating network bottlenecks and roundtrips. Also, by moving procedures from the client to the server you are able to free the client’s resources and take advantage of the server’s computing resources where they will execute even faster. Additionally, stored procedures are compiled once, cached and stored in an executable form which makes them very quick and efficient whenever they are called, and as a result, lowers memory requirements. ### Productivity and ease of use Stored procedures boost productivity by helping you avoid redundant coding – you can group related tasks in a single place and call them whenever you want. They also extend the functionality of the RDBMS (Relational Database Management System), for example, by calling functions from your SQL statements. Furthermore, stored procedures can also be shared between multiple programs, letting you share business logic across applications. ### Security Stored procedures are secure. You can allow users to manipulate data through a stored procedure with predefined privileges. For example, you can allow users to access a stored procedure that updates a table but deny access to the table itself. ## Managing stored procedures in MySQL #### Creating a stored procedure To create a procedure, you can either make use of the MySQL command-line client or MySQL workbench. When creating procedures you wrap a series of SQL queries in the`CREATE PROCEDURE`statement. The syntax is as follows: ``` DELIMITER // CREATE PROCEDURE procedure_name([procedure_parameter[,...]]) BEGIN statements; END // DELIMITER ;Code language: SQL (Structured Query Language) (sql) ``` When writing stored procedures, the general rule of thumb is to: 1. Assign a name for the procedure (procedure_name) 2. Specify a comma-separated list of parameters for the procedure, if any. 3. Write your statements that will be executed by the procedure between the`BEGIN`and`END`block. Because the MySQL statements are usually terminated with the semi-colon`;`, we need to introduce a different delimiter`//`using the`DELIMITER`statement. This will allow you to write the queries you want to execute in a familiar syntax. #### Calling/executing a stored procedure The syntax for executing a stored procedure is simple. You use the`CALL`statement followed by the name of the stored procedure you want to execute: ``` CALL procedure_name()Code language: SQL (Structured Query Language) (sql) ``` #### Listing all created stored procedures You can list all the procedures you have created for a particular database by running the following command: ``` SHOW PROCEDURE STATUS WHERE db = database_name;Code language: SQL (Structured Query Language) (sql) ``` #### Deleting a stored procedure To delete a stored procedure, you can call the`DROP PROCEDURE`statement followed by the name of the stored procedure you wish to delete: ``` DROP PROCEDURE procedure_name;Code language: SQL (Structured Query Language) (sql) ``` #### Altering/modifying an existing stored procedure Currently, there is no statement available to change the parameters or SQL statements in the body of a stored procedure. Because of this reason, we can not alter the content of a stored procedure via the MySQL command-line (except for comments). However, you can do that via the MySQL Workbench GUI using the following steps: 1. In the Navigator pane, right click the name of the stored procedure you want to modify, and select or click Alter Stored Procedure… ![An image showing stored procedures in the MySQL Workbench GUI Navigation pane.](https://coderpad.io/wp-content/uploads/2022/12/img_638d35835d988.png) Altering stored procedures in the MySQL Workbench GUI step 1. 1. In the window that opens, make your changes and click Apply: ![An image showing the stored procedure edit window in MySQL Workbench](https://coderpad.io/wp-content/uploads/2022/12/img_638d3585d3e81.png) Altering a Stored Procedure in the MySQL Workbench GUI step 2 #### Putting it together Let us create a stored procedure using what we learned above. Our first stored procedure example is fairly simple, it fetches the list of films in our database when called. Before we can create a procedure though, we need to tell MySQL to use the database we would like to create the procedure in. In this guide, I will be using the famous [`sakila`database](https://dev.mysql.com/doc/sakila/en/sakila-installation.html), available in the MySQL documentation website. ``` USE sakila;Code language: SQL (Structured Query Language) (sql) ``` The following lines will create a stored procedure named`get_all_films`in the`sakila`database: ``` DELIMITER // CREATE PROCEDURE get_all_films()BEGIN SELECT * FROM film; END // DELIMITER ;Code language: SQL (Structured Query Language) (sql) ``` The following line will then execute the stored procedure and return all the films in the database: ``` CALL get_all_films();Code language: SQL (Structured Query Language) (sql) ``` By defining our film fetching logic in a stored procedure, we can execute the same logic or tasks repeatedly by simply calling`get_all_films()`. It gets even better when we need to do more complex tasks in a single call as you will see in the next example. ## Using parameters in stored procedures MySQL stored procedures support three types of parameters: #### 1. The`IN`parameter This parameter works just like function parameters found in many programming languages like JavaScript, Python, etc.`IN`parameters are the default method to pass values that will be used by the statements in a stored procedure. The value of an`IN`parameter can be passed as values in the statements you write in a stored procedure. The value of an`IN`parameter is always protected, this means that when you modify the value of the parameter anywhere in the body of the stored procedure, the original value remains unchanged. #### 2. The`OUT`parameter The`OUT`parameter is used by the calling program, most times the result of the statements you run in the stored procedure can be stored as a value in the`OUT`parameter and can then be retrieved later. The`OUT`parameter’s original value can also be modified inside the stored procedure. #### 3. The`INOUT`parameter The`INOUT`parameter combines the functionality of the`IN`and`OUT`parameter types. This means that it can be used for passing arguments into the stored procedure and its value can also be retrieved by the calling program. The stored procedure we ran above lacked parameters, so let us create another one that has them so that we can add a bit more enhanced functionality. Let’s say we want to find all films that are in stock in our database; we can add some parameters that can be used in dynamically filtering the data for this particular attribute. This is what the stored procedure would look like: ``` DELIMITER // CREATE PROCEDURE `film_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT) BEGIN SELECT inventory_id FROM inventory WHERE film_id = p_film_id AND store_id = p_store_id AND inventory_in_stock(inventory_id); SELECT COUNT(*) FROM inventory WHERE film_id = p_film_id AND store_id = p_store_id AND inventory_in_stock(inventory_id) INTO p_film_count; END // DELIMITER ;Code language: SQL (Structured Query Language) (sql) ``` In the example above, we introduced three parameters using two of the parameter types –`IN`and`OUT`: 1. `p_film_id`defines the variable for the film’s id 2. `p_store_id`defines the variable for the store’s id 3. `p_film_count`will hold the total number of films that were found in the inventory using the filters passed. We would call the procedure by passing the required arguments as follows: ``` mysql> CALL film_in_stock(1, 1, @p_film_count); +--------------+ | inventory_id | +--------------+ | 1 | | 2 | | 3 | | 4 | +--------------+ 4 rows in set (0.01 sec)Code language: SQL (Structured Query Language) (sql) ``` When we called the procedure, we asked MySQL to run our stored procedure to find all the films in the inventory table, where the film id is 1 and the store id is 1. We also passed a third argument, which is a variable name that will hold the total count value of the results. We can retrieve the results by running a`SELECT`statement followed by the variable name as follows: ``` mysql> SELECT @p_film_count; +---------------+ | @p_film_count | +---------------+ | 4 | +---------------+ 1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql) ``` Note that the variable name we passed as our third argument doesn’t have to be the exact spelling as the parameter we defined when creating the stored procedure. We could as well just use`@p_count`if we wanted to. ## Conclusion From the points and examples above, we can see how very powerful and useful stored procedures can be when used in MySQL. I use them often to avoid repeating tasks that I would love to execute more than once. Apart from saving you time, they also help you move some logic you would normally write in your application to the database, thus enabling you to focus more on the business logic of your application. In our next tutorial, we’d visit some more advanced concepts of using stored procedures in MySQL. Till then, feel free to check out some of our other resources covering performance tuning in MySQL in the links below. [How to Use Indexes to Increase MySQL Database Performance](https://coderpad.io/blog/development/how-to-use-indexes-to-increase-mysql-database-performance/) [How to Optimize MySQL Database Schema for Improved Performance](https://coderpad.io/blog/development/optimize-mysql-database-schema/) [How to Optimize Query Performance in MySQL Databases](https://coderpad.io/blog/development/optimize-query-performance-mysql/) I’m Elvis Duru. I create helpful content for web developers with a focus on React, Node.js, SQL/NoSQL Databases, and more! Let’s connect on [Twitter](https://twitter.com/ElvisDuru). ## References [Advantages of Stored Procedures – Oracle8i Java Stored Procedures Developer’s Guide](https://docs.oracle.com/cd/F49540_01/DOC/java.815/a64686/01_intr3.htm) ## Subscribe for more CoderPad insights ## Trending Articles Previous slide Next slide [Development ## What is Prompt Engineering? Introducing AI tools such as ChatGPT and Bard helps increase the efficiency of tasks such as data generation, code generation and completion, text summarization, etc. How were these AI tools trained to answer almost every question you ask them? The answer, prompt engineering, is discussed in this blog post. Read the post](https://coderpad.io/blog/development/what-is-prompt-engineering/) [Development ## A Guide To Using React’s useCallback() Hook Are you looking to optimize your React applications? Have you heard of the useCallback hook? useCallback is a powerful React hook that helps you optimize your application by preventing unnecessary re-renders. In this post, we'll dive into React's useCallback() hook, define referential equality and callback functions and how to tie it all together. Read the post](https://coderpad.io/blog/development/a-guide-to-using-reacts-usecallback-hook/) [Development ## MySQL vs. PostgreSQL: How Do They Compare? A database is a convenient tool that allows users to access information, maintain records, and apply commonly used operations, such as insertion, modification, deletion, and data organization. Learn about MySQL vs PostgreSQL, their features, advantages, and disadvantages. Finally, check out which one is the best choice for you. Read the post](https://coderpad.io/blog/development/mysql-vs-postgresql-how-do-they-compare/) [Development ## PostgreSQL LIKE Operator: A Detailed Guide When using databases, searching for values that match a given pattern is a familiar necessity. Learn how you can retrieve entries with queries including a particular string using Postgres LIKE operator. Read the post](https://coderpad.io/blog/development/postgresql-like-operator-a-detailed-guide/) [Development ## How To Use DataProvider In TestNG With Examples When it comes to testing there are many tools and techniques available. In this post, we'll discuss how to use DataProvider with TestNG. Read the post](https://coderpad.io/blog/development/how-to-use-dataprovider-in-testng-with-examples/) [Development ## How To Get Up And Running With Django Migrations: A Guide Django migrations are a way of handling the application of changes to a database schema. By following the steps outlined in this guide, you can easily set up and use migrations in your Django projects. Read the post](https://coderpad.io/blog/development/how-to-get-up-and-running-with-django-migrations-a-guide/) [Development ## Writing A Parameterized Test In JUnit With Examples A JUnit parameterized test allows you to decouple data from structure, generating cleaner and more reliable tests. Learn more in this guide. Read the post](https://coderpad.io/blog/development/writing-a-parameterized-test-in-junit-with-examples/) [Development ## How To Use Assert In JUnit With Examples The JUnit Assert class is what you use to write unit testing assertions in JUnit. Learn more about it in this guide, full of examples. Read the post](https://coderpad.io/blog/development/how-to-use-assert-in-junit-with-examples/) [Development ## A Simple Guide To TypeORM: What It Is And How To Use It Considering current languages have object-oriented programming features, most database management systems are based on the relational model, with tables instead of objects. For these reasons, mapping relational objects is very important in development. In this article, you will learn what TypeORM, one of the main tools used today, is. Read the post](https://coderpad.io/blog/development/a-simple-guide-to-typeorm-what-it-is-and-how-to-use-it/) [Development ## Using PreparedStatement in JDBC With Examples In this post, we'll see why and how to work with JDBC and PreparedStatement. We will also learn along with examples using code. Read the post](https://coderpad.io/blog/development/using-preparedstatement-in-jdbc-with-examples/) [Development ## React vs React Native: The Differences Explained React and React Native are similar in that they both allow web developers to build applications based on the unique concept of components. But the two frameworks do this in radically different ways. In this post, we cover the key differences between react and react native frameworks, implementation, and which is an appropriate choice. Read the post](https://coderpad.io/blog/development/react-vs-react-native-the-differences-explained/) [Development ## Logging in Django The most challenging part of fixing a problem is identifying it. Unfortunately, this statement is exceptionally true when it comes to debugging. Learn how to use the power of logging into your Django applications to improve your debug process in this article. Read the post](https://coderpad.io/blog/development/logging-in-django/) ## Hire with confidence. See why engineering teams trust CoderPad to evaluate developers with realistic, AI-ready hiring workflows. [Request a demo](https://coderpad.io/sales/)