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.
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:
1
CREATE
2
[DEFINER = user]
3
PROCEDURE [IF NOT EXISTS] <stored_procedure_name> ([<io_parameter>[,...]])
4
<procedure_body>
Where:
For example, this is how you can create a stored procedure to get the top five users in MySQL:
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:
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.
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:
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.
This will open the following popup, showing an SQL template for your stored procedure.
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.
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:
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:
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)
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.
Otherwise, you can manually launch the MySQL DROP PROCEDURE query in DbVisualizer - run the following query: DROP PROCEDURE getTopUsers
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 SELECT, INSERT, UPDATE, 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:
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.
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.
