SQL Topics
Procedure vs Function
title: Procedure vs Function
In the previous lessons, you learned:
Stored Procedures
Create Procedure
Procedure Parameters
Execute ProcedureStored Procedures are powerful database objects used to perform business operations.
However, SQL databases also provide another important object called:
FunctionMany beginners confuse:
Stored Procedure
and
Functionbecause both contain SQL code and can perform database-related operations.
Although they look similar, they serve different purposes and have different capabilities.
Understanding the difference between Procedures and Functions is essential for database design, optimization, and interviews.
What is a Stored Procedure?
A Stored Procedure is a named block of SQL statements stored in the database that performs one or more operations.
Example:
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT *
FROM Employees;
END;A procedure:
Can Perform Multiple Tasks
Can Modify Data
Can Execute TransactionsWhat is a Function?
A Function is a database object that accepts input values, performs calculations or operations, and returns a value.
Example:
CREATE FUNCTION AddNumbers
(
@A INT,
@B INT
)
RETURNS INT
AS
BEGIN
RETURN @A + @B;
END;Execution:
SELECT dbo.AddNumbers(10,20);Result:
30Why Do We Need Both?
Procedures are used for:
Business Operations
Database Processing
TransactionsFunctions are used for:
Calculations
Transformations
Reusable LogicSimple Analogy
Stored Procedure:
Restaurant KitchenCan prepare many dishes and perform multiple actions.
Function:
CalculatorTakes inputs and returns a result.
Main Difference
Stored Procedure:
Performs ActionsFunction:
Returns ValueStored Procedure Example
CREATE PROCEDURE IncreaseSalary
AS
BEGIN
UPDATE Employees
SET Salary =
Salary + 5000;
END;Purpose:
Modify DataFunction Example
CREATE FUNCTION CalculateBonus
(
@Salary DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
RETURN @Salary * 0.10;
END;Purpose:
Return Bonus ValueProcedure Characteristics
A Stored Procedure:
May Return Data
May Not Return Data
Can Execute Multiple Queries
Can Modify Tables
Can Use TransactionsFunction Characteristics
A Function:
Must Return A Value
Cannot Perform Most Data Modifications
Typically Used In QueriesProcedure Syntax
CREATE PROCEDURE ProcedureName
AS
BEGIN
SQL Statements
END;Function Syntax
CREATE FUNCTION FunctionName
(
Parameters
)
RETURNS DataType
AS
BEGIN
RETURN Value;
END;Return Value Difference
Stored Procedure:
Return Value OptionalFunction:
Return Value MandatoryA function without a return value is invalid.
Calling a Procedure
Procedure execution:
EXEC GetEmployees;Calling a Function
Function execution:
SELECT dbo.AddNumbers
(
10,
20
);Data Modification Difference
Stored Procedure:
UPDATE Employees
SET Salary = 70000;Allowed.
Function:
UPDATE Employees
SET Salary = 70000;Generally not allowed inside functions.
Transaction Support
Stored Procedure:
Supports TransactionsExample:
BEGIN TRANSACTION;
UPDATE Accounts
SET Balance =
Balance - 1000;
COMMIT;Function:
Transaction Control
Not Allowedin most databases.
Using Procedures in Queries
Procedure:
EXEC GetEmployees;Cannot be directly embedded inside most SELECT statements.
Using Functions in Queries
Function:
SELECT
EmployeeName,
dbo.CalculateBonus(Salary)
FROM Employees;Functions can be used inside queries.
Types of Functions
Functions are generally divided into:
Scalar Functions
Table-Valued Functions
Aggregate Functions
Built-In FunctionsThese topics will be covered later.
Comparison Table
| Feature | Stored Procedure | Function |
|---|---|---|
| Purpose | Perform Operations | Return Value |
| Return Value Required | No | Yes |
| Can Modify Data | Yes | Limited/No |
| Can Use Transactions | Yes | No |
| Can Be Used in SELECT | No | Yes |
| Supports Multiple Result Sets | Yes | No |
| Execution Method | EXEC | SELECT |
| Business Logic | Excellent | Limited |
| Performance Calculations | Limited | Excellent |
Real-World Example: Banking
Stored Procedure:
Transfer MoneyTasks:
Debit Account
Credit Account
Insert Transaction LogMultiple operations.
Function:
Calculate InterestInput:
Balance
Interest RateOutput:
Interest AmountReal-World Example: E-Commerce
Stored Procedure:
Create Order
Update Inventory
Generate InvoiceFunction:
Calculate DiscountInput:
Product Price
Discount PercentageOutput:
Discounted PriceReal-World Example: Payroll
Stored Procedure:
Generate Monthly PayrollFunction:
Calculate TaxReturns tax amount.
Real-World Example: Hospital System
Stored Procedure:
Register Patient
Assign Doctor
Generate AppointmentFunction:
Calculate Patient AgeReturns age value.
When Should You Use a Procedure?
Use Stored Procedures when:
Multiple SQL Statements Required
Transactions Needed
Data Modification Required
Complex Business Logic ExistsWhen Should You Use a Function?
Use Functions when:
Calculation Required
Single Value Needed
Logic Used Inside Queries
Reusable Formula NeededAdvantages of Stored Procedures
Supports Complex Operations
Handles large workflows.
Better Transaction Control
Supports COMMIT and ROLLBACK.
Strong Security
Can restrict direct table access.
Centralized Business Logic
Easy maintenance.
Advantages of Functions
Reusable Logic
Write once, use everywhere.
Can Be Used Inside Queries
Major advantage.
Excellent for Calculations
Perfect for formulas.
Cleaner SQL Queries
Improves readability.
Disadvantages of Stored Procedures
Cannot Be Used Directly in SELECT
Limited query integration.
More Complex
Large procedures become difficult to manage.
Disadvantages of Functions
Limited Data Modification
Restricted operations.
No Transaction Control
Cannot manage transactions like procedures.
Less Suitable for Complex Workflows
Focused mainly on calculations.
Common Interview Questions
What is the main difference between a Procedure and a Function?
A procedure performs operations, while a function returns a value.
Can a Function return multiple result sets?
No.
Functions typically return a single value or table.
Can a Procedure modify data?
Yes.
Procedures can perform INSERT, UPDATE, and DELETE operations.
Can Functions be used inside SELECT statements?
Yes.
That is one of their biggest advantages.
Which is better for transactions?
Stored Procedures.
Summary
Stored Procedures and Functions are both important database objects, but they serve different purposes. Procedures are designed for executing business logic and database operations, while Functions are designed for calculations and returning values.
In this lesson, you learned:
- What Procedures are
- What Functions are
- Procedure vs Function differences
- Execution methods
- Return values
- Transaction support
- Real-world examples
- Advantages and disadvantages
- Best practices
Mastering the difference between Procedures and Functions is essential because enterprise applications use both extensively for building scalable and maintainable database systems.
Next Step
Continue to the next module:
Trigger Introduction →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Procedure vs Function.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, stored, procedures
Related SQL Topics