Photo by Carolina Pimenta on Unsplash
SQL - Stored Procedures vs Functions
Exploring stored procedures and functions, as well as their respective functionalities.
Introduction
In this article, we're going to discuss the differences between stored procedures and functions. Before we begin comparing stored procedures with functions in SQL, I'd like to explain them briefly.
Stored Procedures
A stored procedure is a collection of SQL statements that are stored in a database to perform some actions (business logic) or any database-related task.
Below, I've created a stored procedure with the name GetUsers
that simply queries a USERS
table and retrieves all users.
DELIMITER $$
CREATE PROCEDURE `GetUsers`()
BEGIN
SELECT * FROM USERS;
END$$
DELIMITER;
Functions
Functions may take arguments, perform calculations or operations, and return the result.
Mainly there are two types of functions:
- Built-in or System functions
- User Defined functions
Built-in Functions
There are a lot of built-in functions available in the databases, such as count
, aggregate
, date
, string
, and so on. Some of them are listed below, along with their definitions.
MIN() -- Returns the minimum value
MAX() -- Returns the maximum value
COUNT() -- Returns the count value
SUM() -- Returns the summation value
AVG() -- Returns the average value
User Defined Functions
Below, I've created a user-defined function with the name GetUserName
which takes UserID
as a parameter and returns the user's name from a USERS
table.
DELIMITER $$
CREATE FUNCTION `GetUsername`(UserID int)
RETURNS varchar(32)
DETERMINISTIC
BEGIN
DECLARE Username varchar(32);
SELECT NAME INTO Username FROM USERS WHERE ID = UserID;
RETURN Username;
END$$
DELIMITER;
Comparing the two...
After gaining a basic understanding of SQL stored procedures and functions, It's time to see how the two stack up in terms of the important functionalities listed below.
Calling
Stored Procedures can call functions, but functions cannot call stored procedures.
CALL GetUsers(); -- Calling a store procedure
Functions can be called inline from SELECT
, UPDATE
, DELETE
, and INSERT
queries, but stored procedures cannot.
SELECT GetUsername(12); -- Calling a user defined function with a parameter
Removing
The methods for removing stored procedures and user-defined functions are listed below.
DROP PROCEDURE `GetUsers`; -- Removing a stored procedure
DROP FUNCTION `GetUsername`; -- Removing a user defined function
Return Values
Stored procedures may return multiple values but functions only return a single scalar value or a table.
Functions always return a value, whereas stored procedures may or may not.
Parameters
Stored procedures support two types of parameters: Input and Output.
Functions on the other hand only support input parameters.
Restrictions
Stored procedures can modify the database by using ALTER
, UPDATE
, DELETE
, etc commands.
Since functions can only use SELECT
statements, they cannot change the database state.
Transactions
Stored procedures allow the use of transactions, and we can write logic to roll back/commit transactions to init
.
Functions cannot make use of transactions.
Exception Handling
Stored procedures support try-catch blocks and exception handling can also be written in init
.
Functions, on the other hand, do not support init
.
Summary
After reading this article, you will gain a good understanding of stored procedures and functions.
In addition, an example-based approach to using these highly powerful techniques is offered, along with information on when and where to apply them.
About Me 👨💻
I'm Faiz A. Farooqui. Software Engineer from Bengaluru, India. Find out more about me @ faizahmed.in
Did you find this article valuable?
Support Faiz A. Farooqui by becoming a sponsor. Any amount is appreciated!