What is Stored Procedure in SQL and why should we use it?

What is Stored Procedure?

A stored procedure is a group of pre-compiled SQL statements in a single execution plan. It’s created and stored in the database so you can reuse it later.

Take a simple example, to show all records from 3 different tables in a MySQL database, instead of writing three separate select queries, we can create a stored procedure proc1:

CREATE PROCEDURE proc1()
BEGIN
    SELECT * FROM table1;
    SELECT * FROM table1;
    SELECT * FROM table3
END;

To execute this procedure, run this statement:

CALL proc1;

It’s just that simple.

Why should you use the stored procedure?

Using stored procedures has some notable benefits:

Less duplicated work. The procedure is created once, stored in the database. To reuse, you just have to call that procedure instead of rewriting the whole list of statements every time you need. 
Faster execution. Because it’s a batch of pre-compiled statements stored in a memory cache for later use, thus execute faster when reuse without having to re-optimize. 
Minimum network traffic. Stored procedure packs multiple SQL statements into one single statement to execute. It’s also stored directly in the database, thus reducing communication over the network. 
Better security. Using stored procedures is considered the most effective way to prevent SQL Injection because stored procedure parameters will be treated as data even if the attacker managed to insert SQL commands into them.

Most major relational databases support stored procedures, however, there might be some minor differences in the syntax. So please be sure to check the manual before using them.

Need a good GUI Tool for relational database? Try TablePlus. It’s is a modern, native GUI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server… faster and easier. 