What is Stored Procedure vs Function in SQL with Examples?

Introduction

When working with databases like SQL Server, MySQL, or PostgreSQL, developers often need to reuse logic, improve performance, and keep database operations organized. Two commonly used database objects for this purpose are Stored Procedures and Functions.

Both are used to encapsulate SQL logic, but they serve different purposes and behave differently.

In this article, we will understand the difference between Stored Procedure and Function in SQL in simple words, along with examples, real-world scenarios, and when to use each.

What is a Stored Procedure in SQL?

A Stored Procedure is a pre-written set of SQL statements that you can execute whenever needed.

It is stored inside the database and can perform operations like inserting, updating, deleting, or fetching data.

Simple explanation:

It is like a reusable program inside the database
Can perform multiple operations
Can return zero, one, or multiple results

Example of Stored Procedure:

CREATE PROCEDURE GetAllUsers
AS
BEGIN
    SELECT * FROM Users;
END;

To execute:

EXEC GetAllUsers;

What is a Function in SQL?

A Function is also a reusable SQL block, but it is mainly used to return a value.

Functions are usually used inside queries.

Simple explanation:

Always returns a value
Used inside SELECT statements
Cannot perform heavy operations like stored procedures

Example of Function:

CREATE FUNCTION GetUserCount()
RETURNS INT
AS
BEGIN
    DECLARE @count INT;
    SELECT @count = COUNT(*) FROM Users;
    RETURN @count;
END;

Usage:

SELECT dbo.GetUserCount();

Key Differences Between Stored Procedure and Function

Let’s understand the difference in simple terms.

1. Return Type

Stored Procedure:

Can return multiple values or result sets
Does not need to return anything

Function:

Must return a value
Returns a single value or table

2. Usage in Queries

Stored Procedure:

Cannot be used directly inside SELECT

Function:

Can be used inside SELECT, WHERE, and JOIN

Example:

SELECT dbo.GetUserCount();

3. Data Modification

Stored Procedure:

Can INSERT, UPDATE, DELETE

Function:

Cannot modify database data (in most cases)

4. Error Handling

Stored Procedure:

Supports TRY...CATCH for error handling

Function:

Limited error handling support

5. Performance

Stored Procedure:

Better for complex operations

Function:

Best for calculations and simple logic

Types of Functions in SQL

Scalar Function

Returns a single value.

Example:

CREATE FUNCTION GetDiscount(@price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @price * 0.1;
END;

Table-Valued Function

Returns a table.

Example:

CREATE FUNCTION GetActiveUsers()
RETURNS TABLE
AS
RETURN (
    SELECT * FROM Users WHERE IsActive = 1
);

Real-World Example

Let’s take an e-commerce application.

Stored Procedure:

PlaceOrder
UpdateInventory
ProcessPayment

These require multiple operations, so stored procedures are best.

Function:

CalculateDiscount
GetTaxAmount

These return values and are used inside queries.

When Should You Use Stored Procedure?

Use stored procedure when:

You need to perform multiple database operations
You want better performance for complex logic
You need transaction handling

When Should You Use Function?

Use function when:

You need to return a value
You want to use logic inside SQL queries
You are performing calculations

Advantages of Stored Procedure

Better performance for large operations
Reusable logic
Supports transactions and error handling

Advantages of Function

Can be used inside queries
Clean and reusable
Ideal for calculations

Disadvantages of Stored Procedure

Cannot be used inside SELECT
More complex to manage

Disadvantages of Function

Limited capabilities
Cannot handle complex operations

Summary

Stored Procedures and Functions are both important in SQL for writing reusable and efficient database logic. Stored procedures are best for performing complex operations like insert, update, and delete, while functions are ideal for returning values and performing calculations inside queries.

Understanding when to use each helps in building scalable, maintainable, and high-performance database applications.