A stored procedure is a prepared SQL code that is stored in our Schema to reuse whenever we need to.
Most SQL databases have stored procedures as a built-in tool as part of SQL’s original language. Nevertheless, stored procedures SQL coding might be slightly different between the SQL databases.
Today we are going to learn how to make different kinds of SQL statements inside a Stored Procedure in MySQL.
Why should I use Stored Procedures?
Maybe you are using PHP with MySQL and you always write the same code in order to update your sales within a period or you need a quick report of it. You could save a query you use always inside the stored procedure and add two parameters date type, which will indicate the start and the end of the period you are consulting. Or let’s say you have a query that inserts new users inside the database and you always do it the same way just changing a few parameters, then you could use stored procedures to make this happen.
One HUGE advantage of using Stored Procedures is that you can use programming statements, for instance, if a parameter variable is ‘S’ then we will execute one query, if it is ‘N’, then we will execute another query.
How to start your MySQL Stored Procedure?
One good way of starting your Stored Procedure is opening your Adminer console (If you don’t have Adminer installed, the code will also work on the console or another interpreter, but Adminer comes with an option that makes the insertion of variables easier).
At this point, I am assuming you have your test environment created and running, but if that’s not your case, here is a little tutorial on how to install MySQL on a Docker container.
Creating a Stored Procedure with Adminer
For our first MySQL Stored Procedure. First of all, we are going to create a table called users_table with three fields: id, name, and surname.
Once you are in Adminer, after selecting your SQL database you will have an option that says create procedure, just click there.
After clicking Create Procedure you will see the next page:
We will add a name to our MySQL Stored Procedure and then we will add three parameters, one will be ‘pName’, the second one will be ‘pSurname’ and the third one will be pId. Your stored procedure should look like this by now:
What we want to achieve is that if the parameter id is 0 then we will insert a record in our table with our Stored Procedure, case else, we will update the existing record in our table with our stored procedure.
The SQL SECURITY INVOKER statement
A stored procedure can either have this statement or will use as default a DEFINER statement, this means that will run with the permissions of the DEFINER of the Stored Procedure. If the DEFINER doesn’t have access to the table you are trying to access, your Stored Procedure will likely don’t work… Unless… You define an SQL SECURITY INVOKER statement at the start of the Stored Procedure, which will use the permission of the user that is currently running the stored procedure. So let’s start with this, and add a SQL SECURITY INVOKER to our statement, your stored procedure should look like this:
The BEGIN statement
After defining (Or not) the SQL SECURITY INVOKER statement, stored procedures start with a BEGIN statement, this statement declares the start of our code. So let’s add it.
The IF statement
We could start now by adding SQL code to our stored procedure, but in this MySQL Stored Procedure, we will change our code depending on the id parameter, remember? So in this case in particular we are going to add an IF pId=0 THEN
The INSERT statement
As you may know by now, we need to use the INSERT statement to add a record to a table, so we will use it to create our record with the MySQL Stored Procedure.
INSERT INTO user_table (name, surname) VALUES (pName, pSurname);
The ELSE Statement
As in any programming language, if the condition IF is not accomplished, then we will do an ELSE statement (So if pId is not 0, then we will do other code)
The UPDATE Statement
The UPDATE statement is the way to update a row in the table, so we are going to use it to update our user table within our MySQL Stored Procedure
UPDATE user_table SET name=pName, surname=pSurname WHERE id = pId; 
The END IF Statement
We have to close our IF statement now, we have done everything we where going to do in here.
A final SELECT statement
So after adding or updating our registry, we will want to see if everything went ok in our tests, so we will put here a SELECT statement
SELECT * FROM user_table;
At last, the END Statement
So after writing our MySQL Stored Procedure, we will tell MySQL that we have finished, we do this by adding an END at the end :P.
Congrats! You have FINALLY finished your Stored Procedure! Now save it with the save button.
Executing a Stored Procedure
The command to execute a Stored Procedure will depends in the SQL database you are using, but in the case of MySQL, you use the keyword CALL
Open the SQL command writer in Adminer and write the next query:
CALL add_names('Peter','Parker',0); -- My Stored Procedure is called add_names, check if you use the same nameThis will create a new registry in our table and you can see what has been write because of the SELECT statement we add at the end, remember?
Now let’s update that record and change the name to Gomez Addams
CALL add_names('Gomez','Addams',1);If all went well, here is your new output:
Congrats! You have finally proved that your first MySQL Stored Procedure is working alright!
What if I don’t have Adminer installed?
Ok, so your test environment doesn’t have Adminer, so we will make this same Stored Procedure but using our console.
The DELIMITER Statement
As you may know, MySQL use ; (semi-colon) as its default DELIMITER but when we come to Stored Procedures, we use them inside it, so we will need to change this to write our Stored Procedure by command line
mysql > DELIMITER $$Creating our Stored Procedure with the CONSOLE
After changing the DELIMITER, we are going to write our stored procedure, the main difference is that here we will have to define our parameters and types by hand and at the END statement, we will use our new delimiter, so the code will look like this:
mysql > CREATE PROCEDURE add_names (IN pName varchar(100), IN pSurname varchar(100), pId INT)
SQL SECURITY INVOKER
BEGIN
IF pId=0 THEN
INSERT INTO user_table (name, surname) VALUES (pName, pSurname);
ELSE
UPDATE user_table SET name=pName, surname=pSurname WHERE id = pId;
END IF;
SELECT * FROM user_table;
END$$
mysql > DELIMITER ; -- DON'T FORGET TO CHANGE AGAIN YOUR DELIMITER AFTER CREATING YOUR STORED PROCEDUREAnd that’s it! Your MySQL stored procedure has been created! You can execute it using the CALL command as we used it before.
Conclusion
MySQL Stored Procedures are powerful tools to make changes with different statements, we indagate only in the IF Statement, but you can also use WHILE – END WHILE statements, IF – ELSEIF – ELSE – END IF Statements, and LOOP statements.
If you want to know more about Stored Procedures I encourage you to use MySQL official documentation to start learning (An advice: Check also LOOP Statement, WHILE Statement, and IF Statement, which will be very useful for your nexts MySQL Stored Procedures!)
Thank you for reading, as always, see you in the next one!
