Stored Procedures vs Functions in SQL: Understanding the Differences

We will often come across Stored Procedures and Functions while working with SQL, which is one of the most popular query language. Stored Procedures and Functions, we use both to store and execute SQL queries. However, they have different purposes.

In this article, we will discuss the difference between them in a simple and beginner friendly way. We will provide detailed explanations, real world examples and some questions for you to practice.

1. What is a Stored Procedure?

A Stored Procedures collects multiple SQL statements together. These SQL statements are stored in the database and the Stored Procedure executes them as one single unit. This is beneficial when we need to perform same steps repeatedly. For example, in a food recipe, there are many steps that we need to perform. For cooking the same recipe, all those steps need to be performed in the exact same order.

A Stored Procedure will collect all these steps as a single unit and when called, it will execute them with just one command.

Features of Stored Procedures:

• Capable of performing multiple actions like inserting, updating or deleting data.
• Can be used to return multiple result sets.
• Can have input and output parameters to pass data in and out.
• Adept at using loops and conditional logic for complicated workflows.
• Frequently used to execute transactions (COMMIT and ROLLBACK) to take care of data integrity.
• We can not use it directly inside SQL queries.

Example of a Stored Procedure:

Let’s have a look at below query to understand Stored Procedure –

"CREATE PROCEDURE GetCustomersByCity (@City NVARCHAR(50))
AS
BEGIN
    SELECT * FROM Customers WHERE City = @City;
END;"

Explanation – This query first created a stored procedure named GetCustomersByCity. It took one parameter – @City (which is a string up to 50 characters here) and then it retrieved all records from the Customers table where the City column matched with the given parameter. Here, the BEGIN and END keywords defined body of the procedure and ensured the SELECT statement runs when the procedure is executed.hence, this stored the set of queries as a procedure.

Now, in order to execute this stored procedure, all we need to write is –

"EXEC GetCustomersByCity 'New York';"

And that’s it. Imagine how much time we will save with Stored Procedure if we were manually writing the above set of queries 50 times a day.

Advanced Example with Multiple Operations:

Below SQL script is defining a Stored Procedure named ‘UpdateAndFetchCustomer’, which first updates a customer’s email address and then retrieves the updated record.

"CREATE PROCEDURE UpdateAndFetchCustomer (@CustomerID INT, @NewEmail NVARCHAR(100))
AS
BEGIN
    -- Update email
    UPDATE Customers SET Email = @NewEmail WHERE CustomerID = @CustomerID;
    
    -- Return updated customer details
    SELECT * FROM Customers WHERE CustomerID = @CustomerID;
END;

EXEC UpdateAndFetchCustomer 101, 'newemail@example.com';"

2. What is a Function?

A Function in SQL helps in performing calculations and return a result. Visualize Functions as a SQL version of calculator – it takes input, processes it, and as returns give a single value or a table.

Features of Functions:

• Has to return a single value or a table.
• Cannot be used to perform actions like inserting, updating, or deleting data.
• Ok to be used inside SQL queries like SELECT, WHERE, and JOIN.
• Cannot execute transactions (no COMMIT or ROLLBACK).
• Cannot return multiple result sets as results.

Example of a Scalar Function:

Let’s understand Functions with the below query –

"CREATE FUNCTION GetTotalSales (@ProductID INT) 
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @TotalSales DECIMAL(10,2);
    SELECT @TotalSales = SUM(Amount) FROM Sales WHERE ProductID = @ProductID;
    RETURN @TotalSales;
END;"

Explanation – This SQL query created a user-defined function named GetTotalSales, which can calculate the total sales for a particular product. It took one input parameter, @ProductID (an integer) and returned a decimal value. Now inside the function, it declared a variable @TotalSales to store the total sum of the Amount column from the Sales table where the ProductID matched the given input. At last, it returned the computed total sales value.

To use this function in a query, all we have to do is write the below query –

"SELECT dbo.GetTotalSales(101) AS TotalSales;"

Example of a Table-Valued Function:

Have a look at below query –

"CREATE FUNCTION GetOrdersByCustomer(@CustomerID INT)
RETURNS TABLE
AS
RETURN (
    SELECT OrderID, OrderDate, Amount FROM Orders WHERE CustomerID = @CustomerID
);"

To use this function:

"SELECT * FROM dbo.GetOrdersByCustomer(102);"

Explanation – First, this SQL query created a table-valued function called GetOrdersByCustomer. It then took @CustomerID as input and retured a table with OrderID, OrderDate, and Amount from the Orders table for that particular customer. Now, when you run SELECT * FROM dbo.GetOrdersByCustomer(102);, it will retrieve all orders placed by the customer with CustomerID = 102.

3. Key Differences Between Stored Procedures and Functions

Let us summarize the most important differences between the two in below table –

4. When to Use Stored Procedures vs Functions?

It all boils down the below situations –

• Use a Stored Procedure when you have to perform multiple SQL operations like inserting, updating or deleting records.
• Use a Function when you have to return a computed value that can be used in a query.

Example Use Cases:

5. Performance Considerations

Stored Procedures:

• The database engine optimizes stored procedures, which can be quicker for more complicated tasks.
• Because only the procedure call, rather than several queries, is transmitted to the database, they minimize network traffic.
• Performance is enhanced by compiling and storing them in a cached execution plan.

Functions:

• We can use Functions in queries, which makes them highly reusable.
• However, because Functions run for every row in a query, they may occasionally be slower.
• There may be impact on performance because they do not support execution plans in the same manner as stored procedures.

6. Advanced Scenarios and Best Practices

Combine Stored Procedures and Functions

Using functions and stored procedures together is sometimes the best course of action. For instance, Stored Procedure can use a function to retrieve calculated values prior to inserting or updating data. Let’s look at below query –

"CREATE PROCEDURE ProcessOrderWithDiscount (@CustomerID INT, @ProductID INT, @Quantity INT)
AS
BEGIN
    DECLARE @Discount DECIMAL(10,2);
    SET @Discount = dbo.CalculateDiscount(@ProductID, @Quantity);
    
    INSERT INTO Orders (CustomerID, ProductID, Quantity, Discount, OrderDate)
    VALUES (@CustomerID, @ProductID, @Quantity, @Discount, GETDATE());
END;"

Here this SQL stored procedure, ProcessOrderWithDiscount, first takes a customer’s order details (CustomerID, ProductID, and Quantity) as input. It then calculates a discount using the CalculateDiscount function based on the product and quantity. Then, it inserted a new order record into the Orders table, including the calculated discount and the current date.

7. Security Considerations in Stored Procedures

Stored procedures do provide enhanced security as they help in controlling access to sensitive data. Rather than granting users direct access to tables, we can restrict access to stored procedures that interact with the data. This will ensure that users can only execute predefined operations and they can not modify the database structure.

Advantages of Using Stored Procedures for Security:

• Prevents SQL Injection: Stored procedures are less susceptible to SQL injection attacks because they employ parameterized queries.
• Access Control: Stored procedures can have their execution rights granted or revoked without disclosing the underlying tables.
• Encapsulation of Business Logic: By enforcing crucial business rules inside the stored procedure, we decrease the possibility of inadvertent changes.

For ex, rather than giving direct access to the Employees table, we can create a stored procedure that retrieves only necessary information:

Then, we can grant execution permissions:

Now, the HRUser can retrieve employee details but they can not access table directly, thereby improving security.

8. Performance Optimization with Stored Procedures

Stored procedures are capable of significantly improving database performance by easing out network traffic and optimizing query execution steps.

Example of Performance Optimization:

Imagine we need to generate a sales report for a particular month. Rather than running multiple queries separately, we can run a stored procedure that can fetch all required data in one go:

Executing this:

Thus, this approach minimized query execution time and it ensured efficient report generation.

Conclusion

Although they have different uses, stored procedures and functions both contribute to the efficiency and re-usability of SQL code:

• Stored procedures are effective at handling transactions, changing data, and carrying out numerous operations.
• We can use functions inside queries, and they are best for returning calculated values.

Hence, we can efficiently optimize your SQL programming and database performance by being aware of their distinctions and use cases.

I hope this guide clarifies the differences. Cheers to coding! 🚀

Also check out our other articles –

1. SQL – Common Interview Questions and Answers
2. How to Use Group By and Having in SQL (Beginner Friendly Guide)
3. SQL JOINS explained with Examples

Visit this page if you need help with SQL server downloads