MySQL
STORED PROCEDURE

Stored Procedures in SQL: A Complete Tutorial

intro

Let’s learn everything you need to know about stored procedures in SQL. In this article, you will see what a stored procedure in SQL is, how to define stored procedures, why you should take them into consideration, and how to use them in a complete example.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

A stored procedure in SQL is a special database object that allows you to store SQL code in a database, assign it a name, and run it again and again. Stored procedures in SQL are a popular solution supported by all major RDBMS technologies.

With a simple SQL command, you can call a stored procedure by its name and execute the SQL statements defined in its body. This powerful tool enables you to move business logic from the application layer to the faster database layer. Follow this guide and become an expert on stored procedures in SQL!

What Is a Stored Procedure in SQL?

A stored procedure is a set of SQL statements that have a name and are stored in a database by an RDBMS (Relational Database Management System.) An SQL stored procedure can be thought of as a group of SQL instructions that can be easily and repeatedly executed with simple commands.

In general, a stored procedure in SQL can accept parameters as input. So, SQL stored procedures can act differently based on the values of the input parameters. Also, it can return one or more values as output, depending on the DBMS implementation. Usually, a SQL stored procedure returns a set of data to the caller.

In other words, a stored procedure is stored SQL code you can reuse many times. So, if you have a set of SQL queries that you write over and over again, you can save them as a stored procedure. Then, you can call the stored procedure in SQL with a simple command to execute those queries when needed.

You can adopt stored procedures to move logic from the application layer to the database layer. Considering that performing operations at the database level is generally more efficient than at the application level, stored procedures help you save time and memory.

This is just one of the many benefits of using stored procedures -you will learn more soon. But now, let's understand how to define a stored procedure in SQL.

How to Create a Stored Procedure in SQL

The syntax to create and run stored procedures is different from RDBMS to RDBMS. At the same time, the general concepts are the same. Thus, a MySQL example should be enough to understand how to create a stored procedure in SQL.

In MySQL, you can create a stored procedure with the CREATE PROCEDURE statement as follows:

Copy
        
1 CREATE 2     [DEFINER = user] 3     PROCEDURE [IF NOT EXISTS] <stored_procedure_name> ([<io_parameter>[,...]]) 4     <procedure_body>

Where:

  • <stored_procedure_name> represents the name assigned to the SQL procedure that will be used to call the stored procedure.
  • <io_parameter> contains the optional list of input, output, and input/output parameters used by the procedure. Each parameter has a unique name. Note that you can reference a parameter in the body of the stored procedure by its name.
  • <procedure_body> contains the SQL statements that define what the stored procedure does.

For example, this is how you can create a stored procedure to get the top five users in MySQL:

Copy
        
1 CREATE PROCEDURE getTop5Users() 2 BEGIN 3     SELECT 4         id, nickname, points 5     FROM 6         users 7     ORDER BY 8         points DESC 9     LIMIT 10         5; 11 END

Here, getTop5Users is <stored_procedure_name>, the list of <io_parameter>s is empty, and <procedure_body> is:

Copy
        
1 SELECT 2     id, nickname, points 3 FROM 4     users 5 ORDER BY 6     points DESC 7 LIMIT 8     5;

Generally, you can run a MySQL stored procedure with the CALL statement as follows: CALL <stored_procedure_name> [(<io_parameter_value> [,...])]

So, continuing the example shown before, you can run the getTop5Users() MySQL stored procedure with: CALL getTop5Users(). This will return the list of the top five users sorted by the number of points.

As you just learned, defining a stored procedure in SQL is not that complex. As you are about to learn, stored procedures are a powerful tool that allows you to perform complex database-level operations. Let’s now understand why you should use them.

Benefits of Stored Procedures

There are three good reasons to adopt a stored procedure in SQL - these three reasons are performance, security, and centralizing the business logic. Now let’s dive deeper into them.

Performance

Performing data retrieval, writing, updating, or deletion operations directly in a database is usually faster than doing it at the application layer. So, moving your business logic from the application to the database can bring significant performance advantages.

Also, calling a stored procedure reduces network traffic. This is because the database runs the stored procedure, produces the results, and sends them back to the application. This operation only involves a single network roundtrip. On the other hand, performing one query at a time at the application level to process the data there involves sending many more network packets.

Security

Generally, applications require both read and write access to a database. This means that applications can run all SQL statements to a database directly. When developers forward data staring into those queries, this can lead to security issues. One of the most popular is SQL injection.

On the other hand, if you centralize the logic in a stored procedure, you can include INSERT and UPDATE statements within the procedure. Then, you can grant applications the privileges to run the procedures. In this way, applications no longer need write access.

Centralizing your business logic

Several applications can connect to the same database. So, if you store the business logic in stored procedures, many applications will have access to them. This means that you will be able to make the business logic consistent across all of the applications.

DbVisualizer logo

If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.

In other words, different applications can call the same stored procedures and get the same results without having to replicate business logic at the application layer many times. So, stored procedures also make your SQL code more reusable and easier to maintain.

Stored Procedures in SQL: A Complete Example

Let’s now how to create, update, run, and drop a stored procedure in SQL with DbVisualizer through an example based on MySQL. Note that the steps to be followed in DbVisualizer remain the same regardless of the RDBMS technology in use. Dealing with stored procedures in SQL is not easy. So, to make SQL stored procedure easier, you should adopt an advanced database client.

Creating a stored procedure

Let’s assume you want to create the following stored procedure:

Copy
        
1 CREATE PROCEDURE getTopUsers(IN topLimit INT) 2 BEGIN 3     SELECT 4         id, nickname, points 5     FROM 6         users 7     ORDER BY 8         points DESC 9     LIMIT 10         topLimit; 11 END

This returns the list of the first topLimit users according to their points.

Creating a new stored procedure in DbVisualizer is easy. Right-click on the “Stored procedures” menu voice and select the “Create Procedure…” option.

Creating a new stored procedure in DbVisualizer.
Creating a new stored procedure in DbVisualizer.

This will open the following popup, showing an SQL template for your stored procedure.

Creating a stored procedure in database connection: MySQL.
Creating a stored procedure in DbVisualizer

Note that the query in the “SQL Preview“ section contains the @delimiter DbVisualizer command. This calls the MySQL DELIMITER command behind the scene to temporarily change the default semicolon ; delimiter used by MySQL to separate statements. You need this because an SQL stored procedure may consist of multiple statements separated by a semicolon ;. Since you want to execute all of them at once, you have to temporarily change the semicolon delimiter with a new delimiter. In this example, the new delimiter is %%%.

Copy the SQL statement, paste it into the “SQL Commander” tab, and update the query accordingly.

Pasting an SQL statement into the SQL Commander tab to create a stored procedure.
Creating a stored procedure in database connection: MySQL.

Then, click the run button to create your stored procedure. DbVisualizer will show a “Success” status in the Log section.

Now, if you reconnect to your database or update the object tree, you will see a getTopUsers option under the “Stored Procedures” dropdown. If you double-click on getTopUsers, you will have access to the following window:

View in DbVisualizer after double-clicking the getTopUsers under the “Stored Procedures” dropdown.
View in DbVisualizer after double-clicking the getTopUsers under the “Stored Procedures” dropdown.

Updating a stored procedure

In the window above, change the SQL query and click the save button. This will automatically run the desired ALTER query and update the SQL stored procedure accordingly in all RDBMSs supporting this feature. Since MySQL does not support the ALTER query on stored procedures, you cannot change the body of a stored procedure. Thus, DbVisualizer will drop the stored procedure and create a new one with the same name.

Running a stored procedure

You have two ways to call a stored procedure in DbVisualizer. First, you click on the run button in the stored procedure tab. This will open the popup window below:

Popup window for running a stored procedure in DbVisualizer.
Popup window for running a stored procedure in DbVisualizer.

Here, you can give all the stored procedure parameters a value and launch it by clicking on the “Continue” button.

Otherwise, you can simply run the following CALL query in the “SQL Commander tab”: CALL getTopUsers(3)

Running a stored procedure in the “SQL Commander tab”.
Running a stored procedure in the “SQL Commander tab”.

At the bottom of the DbVisualizer window, you will see the results returned by the stored procedure. In detail, those are exactly the top 3 users sorted by the number of points.

Dropping a stored procedure

If you want to delete a stored procedure, right-click on it, and select “Drop Procedure…”. This will run a DROP query and remove the procedure for you.

Dropping a stored procedure.
Dropping a stored procedure.

Otherwise, you can manually launch the MySQL DROP PROCEDURE query in DbVisualizer - run the following query: DROP PROCEDURE getTopUsers

Deleting a stored procedure by destroying the getTopUsers stored procedure.
Deleting a stored procedure by destroying the getTopUsers stored procedure.

Doing so will destroy the getTopUsers stored procedure and you will not be able to call it anymore.

Conclusion

Here, you saw everything you should know about SQL stored procedures. Specifically, you understood what a stored procedure is in SQL, why you should be able to use stored procedures, and how to do it in a guided example.

As shown above, dealing with stored procedures becomes easier if you adopt a database client, such as DbVisualizer. In particular, DbVisualizer allows you to create a stored procedure visually, as well as update, drop, and run it with simple clicks. DbVisualizer also helps you define and deal with the stored procedure parameters. Plus, you can use DbVisualizer to change the stored procedure permissions with a simple select. Dealing with stored procedures in SQL has never been easier. Try DbVisualizer for free!

FAQ About Stored Procedures

Let’s now answer some questions about stored procedures in SQL.

What databases allow stored procedures?

Most relational database technologies support stored procedures. In detail, you can define stored procedures in MySQL, its flavors like Percona Server and MariaDB, PostgreSQL, Oracle, SQL Server, and DB2. These are the most used and popular RDBMS available.

What is the difference between a stored procedure and a function?

In SQL, stored procedures and functions are two similar but different concepts. A stored procedure must be defined manually, can perform operations that a function may not, and can be invoked only by using a specific command such as CALL or EXECUTE. On the other hand, SQL functions can be used in any SQL SELECTINSERTUPDATE, and DELETE statement. Examples of SQL functions are COUNT()AVG()SUM()UPPER()LENGTH(). Note that you can also manually define new functions.

What are the four most important parts of a stored procedure?

A stored procedure has:

  • A name: used to execute the stored procedure in SQL.
  • Input parameters: an optional list of parameters the stored procedure accepts as input.
  • A body: the SQL code that defines the stored procedure logic.
  • Output parameters: an optional list of one or more parameters returned by the stored procedure when called.

How to execute a stored procedure in SQL Server?

In SQL Server 2019, you can run a stored procedure with the EXECUTE command followed by the name of the stored procedure and its parameters. Note that you can also use EXEC, which is the shortened version of EXECUTE. Learn more about the SQL Server syntax to execute stored procedures.

Dbvis download link img

How to update a stored procedure in SQL?

In some RDBMSs, such as SQL Server, you can update a stored procedure in SQL with an ALTER statement. In other RDBMSs, you have to drop the stored procedure and create it again with a new body and the same name. Note that in RDBMS technologies supporting the ALTER statement on stored procedures, dropping a stored procedure and recreating it with the same name may not be the best approach. The reason is that this approach removes the permissions that have been explicitly granted to the stored procedure.

About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL ORDER BY Clause: Definitive Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-04-22
title

Adding Dates in SQL: A Complete Tutorial

author Antonello Zanini tags DATE DATETIME MySQL POSTGRESQL SQL SERVER 7 min 2024-04-15
title

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11
title

SUBSTRING_INDEX in SQL Explained: A Guide

author Lukas Vileikis tags MySQL SQL 8 min 2024-04-08
title

SQL NOT IN: the Good, Bad & the Ugly

author Lukas Vileikis tags MySQL SQL 7 min 2024-04-04
title

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01
title

SQL CAST Function: Everything You Need to Know

author Antonello Zanini tags CAST MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-03-28
title

MySQL IFNULL – Everything You Need to Know

author Leslie S. Gyamfi tags IFNULL MySQL 6 min 2024-02-26
title

MySQL CREATE DATABASE Statement: Definitive Guide

author Antonello Zanini tags Create database MySQL 7 min 2024-02-08
title

A Complete Guide to SUBSTRING_INDEX in SQL

author Antonello Zanini tags MySQL 6 min 2024-01-16

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗