Stored Procedures

You have already learned about SQL queries. Sometimes we need to insert or update records in multiple tables or do some checks. However, simply writing a lot of the same queries is not a good practice. With stored procedures, you can do it and save yourself a lot of time. Let's take a closer look at these procedures.

What are stored procedures?

A stored procedure is a set of SQL queries that are compiled once and stored within a database management system. In other words, if you have an SQL query that you write over and over again, you can save it as a stored procedure, and then just call it to execute it.

Why do we need stored procedures?

Often, a data operation is a set of instructions that have to be executed in a specific sequence. There are a lot of situations when we need to use some queries multiple times. For example, when we add a product into an order table, we need to do some checks in advance. This requires a lot of queries that we'll have to run several times. In that case, it would be more optimal to encapsulate all these actions into one object-stored procedure.

Stored procedures are used for sequential execution of some SQL queries. Queries that are stored in a procedure are performed step by step like in an instruction. You can put in your procedure as many queries as you'd like.

Stored procedures can help you save some time and make the development process a lot simpler.

How to create a stored procedure?

Now let's talk about how we can create a stored procedure. We will use MySQL in code examples.

For example, let's create a simple table named consoles with console and price columns and fill it with some data:

Great! Now let's create a query that will change price of PlayStation 5 to 1100 in the consoles table and select consoles with price more than 800:

UPDATE 
    consoles
SET 
    price = 1100
WHERE
    console = 'PlayStation 5';

SELECT
    *
FROM
    consoles
WHERE
    price > 800;

Now we will store this query in a stored procedure named UpdatePriceAndSelect. For creating a stored procedure, we need to use CREATE PROCEDURE command. Then to separate the procedure body from the rest of the script, the procedure code is placed in a block BEGIN ... END:

CREATE PROCEDURE UpdatePriceAndSelect ()
BEGIN
    UPDATE 
        consoles
    SET 
        price = 1100
    WHERE
        console = 'PlayStation 5';
    
    SELECT
        *
    FROM
        consoles
    WHERE
        price > 800;
END;

Nice! We've just created our stored procedure! Now we can execute it anytime we want. For that, we use the CALL command:

CALL UpdatePriceAndSelect;

Let's take a look at the result after we executed the procedure:

As you can see, our procedure is working perfectly!

To delete the stored procedure, use the DROP command:

DROP PROCEDURE UpdatePriceAndSelect;

Now you know, how to create stored procedures and use them! Yes, our procedure is very simple, but it's just an example.

Summary

So, a stored procedure is a set of SQL queries, that are stored on the server and can be executed at any time. There're a lot of situations where we can use stored procedures to improve our development process. With stored procedures, developers can easily do some checks, inserts, validation, etc. in multiple tables without rewriting the same queries several times.

Now, let's repeat key syntax in stored procedures:

• You can create a procedure with CREATE PROCEDURE

• Use BEGIN and END to separate procedure from other code

• Run your stored procedure with the CALL command