A Stored Procedure is an user defined SQL code that contains a sequence of SQL instructions. A Stored Procedure can be thought of as a function in other programming languages. Stored Procedures are not part of standard SQL. But, multiple RDBMS providers provide the Stored Procedure functionality.
What is it?
A Stored Procedure is similar to a function in other programming languages. A stored Procedure will have a name. It takes some parameters.It execute a set of SQL instructions.
A stored procedure will take three types of arguments.
IN – variable for storing and accessing input values.
OUT – variable for sending values. outside of stored procedure.
INOUT – variable which can be used for storing both input and output values.
Mainly, stored procedures are used for executing the repeated SQL queries with different parameters.
Advantages of stored procedures
- We can give the user access to only execute a stored procedure to and hence improving the DB security.
- It increases code reusability.
- Makes the code management more efficient in SQL.
Stored Procedure vs Functions in SQL
Function is a feature available in a lot of RDBMS to improve the code reusability just like Stored Procedures. Let’s see the pros and cons of stored procedures and functions.
| Stored Procedures | Functions |
| A stored procedure is not required to return a value | A function is required to return a value |
| It does not have a return type but can retun values | It has a return type and will return a value. |
| The DML queries like INSERT, UPDATE and DELETE can be performed inside a stored procedure. | Only SELECT statements can be performed inside a function. |
| A Stored procedure can call a function | A function cannot call a stored procedure. |
| A Stored Procedure is called using CALL keyword | A function is called using SELECT Statement |
To define a parameter in a stored procedure, we need to use the following syntax.
CREATE PROCEDURE PROCEDURE_NAME(PARAM_TYPE var_name DATA_TYPE,PARAM_TYPE var_name DATA_TYPE)var_name – Name of the parameter we wish to use
PARAM TYPE – it should be one IN,OUT or INOUT
DATA_TYPE – Data type of the variable like int,double, date,datetime,etc.
To learn more about the stored procedure’s syntax in MySQL you can look into the official MySQL documentation.
I will use a table from my local database which contains a list of complaints made by the consumers. In the below image you can see the columns available in the table.
The Problem
We want to fetch the complaints made after year 2014.
To do so, we can use the following query
SELECT * FROM consumer_complaints WHERE YEAR(`Date Received`) > 2014; Great. But now our company wants to fetch the complaints after 2015. To do so, we can modify the query as follows.
SELECT * FROM consumer_complaints WHERE YEAR(`Date Received`) > 2015; We changed the query directly though, only the filter value has changed. Think if our company wants to fetch records after a different year. In that case, We need to change the query once again and run it. This query is not so reusable from a developer perspective. So, How can we improve the reusability of this query?
The solution (Stored Procedures)
Let’s create a stored procedure to resolve the problem. Refer to the below code snippet for creating a stored procedure.
DROP PROCEDURE IF EXISTS get_complaints_after_year;
DELIMITER //
CREATE PROCEDURE get_complaints_after_year (IN filter_year INT)
BEGIN
SELECT * FROM consumer_complaints WHERE YEAR(`Date Received`) > filter_year;
END//
DELIMITER ;Code Breakdown
The first line drops the procedure get_complaints_after_year procedure if it is already created.
The line DELIMITER // changes the delimiter of SQL from ; to //. This is because We will be writing multiple SQL statements in a procedure.If we put a semicolon to end an SQL query. the SQL compiler will misinterpret that like the procedure definition ends there and throw us some syntax error. To avoid this we will use the // as DELIMITER till we complete our stored procedure definition.
CREATE PROCEDURE tells the SQL compiler that we are going to create a stored procedure named get_complaints_after_year and it will take an input parameter named filter_year.
The BEGIN keyword is used to mention that the function definition is starting.
After BEGIN we have a SELECT statement which filters the record against the filter_year value. Now If we want to get the complaints after 2014, we just need to call the stored procedure with 2014 as input parameter and the same goes for 2013.
The END keyword tells the compiler that we are done defining our function.
The // is used as the delimiter the whole function definition process as one whole line.
After completing the function definition, we set the delimiter back to ;.
Now it’s time to call the stored procedure with different filter values.
How to Call a Stored Procedure
Let’s call the stored procedure fetch the records after year 2013 and 2014.
CALL get_complaints_after_year(2013);
CALL get_complaints_after_year(2014); 
We used the CALL keyword followed by the name and parameters for the stored procedure to invoke the procedure. You can see in above example we have not modified anything in the query directly. Yet, we got the desired results that’s the power of Stored Procedures.
Stored Procedures With OUT PARAMETER/Return Value
What if we want to return some value from a stored procedure. That’s when we need to use the OUT parameter in a stored procedure. This will make the stored procedure to return some value. Let’s consider we want to return the number of complaints received after a specified year and access it outside of the stored procedure to do some other processing. To do so, change the stored procedure as follows.
DROP PROCEDURE IF EXISTS get_complaints_after_year;
DELIMITER //
CREATE PROCEDURE get_complaints_after_year (IN filter_year INT,OUT record_count INT)
BEGIN
SELECT * FROM consumer_complaints WHERE YEAR(`Date Received`) > filter_year;
SELECT COUNT(*) INTO record_count FROM consumer_complaints WHERE YEAR(`Date Received`) > filter_year;
END//
DELIMITER ;Now our stored procedure executes two SQL queries and returns result of a query in an OUT parameter. As we have added one more parameter to the function,we should make changes to calling the stored procedure as well.
Calling Stored Procedure With Input and Output Parameters
CALL get_complaints_after_year(2014,@count);
SELECT CONCAT('Record Count: ',@count); In the above code you can see that the procedure is called with 2014 as input and the count of the records is returned in the @count variable. After calling the procedure, I am using a SELECT statement to print the count value.
We should use the INOUT type parameter to receive input and send output using the same variable.
Conclusion
Oh yeah! we have completed learning about stored procedures in MySQL and thanks for reading till the end of the article, if you see any space for improvements or have any doubts feel free to leave them in the comments section
You can read more about databases, in the following link https://engineering.rently.com/architecture/
Related posts:
A programmer.