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 ProceduresFunctions
A stored procedure is not required to return a valueA function is required to return a value
It does not have a return type but can retun valuesIt 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 functionA function cannot call a stored procedure.
A Stored Procedure is called using CALL keywordA function is called using SELECT Statement
Difference between Functions and Stored Procedures

To define a parameter in a stored procedure, we need to use the following syntax.


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;
CREATE PROCEDURE get_complaints_after_year (IN filter_year INT)
   SELECT * FROM consumer_complaints WHERE YEAR(`Date Received`) > filter_year;

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);
stored procedures result 1
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;
CREATE PROCEDURE get_complaints_after_year (IN filter_year INT,OUT record_count INT)
   SELECT * FROM consumer_complaints WHERE YEAR(`Date Received`) > filter_year;
   SELECT COUNT(*) INTO record_count FROM consumer_complaints WHERE YEAR(`Date Received`) > filter_year;

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.


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/

Leave a Reply

Login with