During my data exploration in the employees database, I always find that I frequently do cross checks and drill downs on individual employee records across multiple tables. Rather than rewriting the query (or even copying and pasting the codes over and over), it was easier to have a stored procedure and call it up.
Stored procedure is of the stored routines that you can run on SQL. The other is Stored Function. This article will focus on Stored Procedures first.
Creating Stored Procedures
There are 2 ways to create a stored procedure in MySQL:
1. Write it into a normal sql file and run it
2. Left click on the Stored Procedure menu and click on create Stored Procedure.
If writing in a normal SQL file, creating a procedure generally follows these steps:
1. Drop procedure if exists
2. Define the new delimiter as $$
3. Start by using the CREATE PROCEDURE procedure_name(parameters). This can be created even without parameters for non parametric procedure. More on this in a bit.
4. Place your query that you want to save as a stored procedure in between a BEGIN and END query. Note the normal semi colon delimiter that is used in the query.
5. Place your delimiter after the end query.
6. Remember to set your delimiter back to semi colon.
Right click on the Stored Procedures menu and Refresh All for your new procedure to appear in the list.
Using the second method, eliminates the first and second step for you and will automatically include it when you click Apply after writing your procedure. It also lays out the main structure of the query with CREATE, BEGIN and END
Clicking on Apply leads you to the full procedure query.
A delimiter is a statement terminator and we are very much familiar with the semi colon (;) that is used to end queries. In stored routines, we need to define and use a different delimiter, such as $$ or //. The query procedure will be in between this newly defined delimiter.
Let’s talk about the parameters in the parenthesis after the procedure name. For a non-parametric procedure (like above), you do not have to put anything in the parenthesis. What this means is that if you are querying for a procedure that does not require an input, such as calling for an average salary, or count of titles you can just leave it empty.
If you’re calling for a parametric procedure, you will need to use the ‘IN’ query followed by the name and data type.
‘create procedure’ procedure_name(IN parameter_name data_type). Note that for best practices, the parameter name is designated by p_parameter_name
The procedure above was developed while doing cross checks during the exploration of the employees database.
This allows you to input in data for the query.
And give you the following results, as an example:
You can also specify an ‘OUT’ query to display the result as a selected or specified value, and not as a results table. The difference in the code is that you need to have a ‘SELECT’ and ‘INTO’
The procedure above is an example query to display the employee number for a desired first and last name.
The steps for creating a parametric procedure with an IN and OUT is modified to the following:
1. Drop procedure if exists
2. Define the new delimiter as $$
3. Start by using the ‘create procedure’ procedure_name(IN name data_type, OUT name datatype). Note that you can have multiple IN query as shown above.
4. Place your query that you want to save as a stored procedure in between a BEGIN and END query. The statement for a parametric procedure with an IN and OUT follows a SELECT-INTO query, where you select the output defined in the CREATE PROCEDURE and store it INTO as a parameter. Note the normal semi colon delimiter that is used in the query.
5. Place your delimiter after the end query.
6. Remember to set your delimiter back to semi colon.
Running the procedure will give you the input window and putting in the desired input in as shown below
Will display the following results:
Running Stored Procedure
Once the procedures have been saved or created, it will appear under the ‘Stored Procedures’ in the menu bar. We can simply run the stored procedure by hovering on the desired procedure and click on the mini lightning sign that appear beside it.
Any changes or modifications to the procedure can be made by clicking on the wrench sign beside the lightning.
