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.
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 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
There are a lot of built-in functions available in the databases, such as
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
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.
Stored Procedures can call functions, but functions cannot call stored procedures.
CALL GetUsers(); -- Calling a store procedure
Functions can be called inline from
INSERT queries, but stored procedures cannot.
SELECT GetUsername(12); -- Calling a user defined function with a parameter
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
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.
Stored procedures support two types of parameters: Input and Output.
Functions on the other hand only support input parameters.
Stored procedures can modify the database by using
DELETE, etc commands.
Since functions can only use
SELECT statements, they cannot change the database state.
Stored procedures allow the use of transactions, and we can write logic to roll back/commit transactions to
Functions cannot make use of transactions.
Stored procedures support try-catch blocks and exception handling can also be written in
Functions, on the other hand, do not support
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.
Thank You :)