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:

1. Built-in or System functions
2. 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