SQL Notes
Learn the differences between Stored Procedures and Functions in SQL, understand use cases, syntax, return values, performance, and real-world examples.
Both stored procedures and functions store SQL logic in the database for reuse. They look similar — both accept parameters and contain SQL statements. But they serve fundamentally different purposes, and knowing when to use each one is a critical skill for database development.
Think of it this way: a function is like a calculator — you give it inputs, it computes something, and hands you back a result. A procedure is like a worker — you tell it what to do, and it performs actions that might include inserting, updating, deleting, and returning multiple things.
What is a Stored Procedure?
A stored procedure performs one or more actions. It can modify data, manage transactions, return multiple result sets, and use output parameters. It is designed to do work.
-- SQL Server: Procedure that performs actions
CREATE PROCEDURE TransferFunds
@FromAccount INT,
@ToAccount INT,
@Amount DECIMAL(10,2)
AS
BEGIN
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @FromAccount;
UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ToAccount;
INSERT INTO TransactionLog (FromAcc, ToAcc, Amount, TransDate)
VALUES (@FromAccount, @ToAccount, @Amount, GETDATE());
COMMIT;
END;-- Execute a procedure
EXEC TransferFunds @FromAccount = 1001, @ToAccount = 1002, @Amount = 5000;What is a Function?
A function computes and returns a value. It is designed to calculate something and give you the answer. Functions can be used inside SELECT statements, WHERE clauses, and other SQL expressions — procedures cannot.
-- SQL Server: Function that calculates and returns a value
CREATE FUNCTION CalculateTax(@Amount DECIMAL(10,2), @TaxRate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
RETURN @Amount * @TaxRate / 100;
END;-- Use function inside a query
SELECT
ProductName,
Price,
dbo.CalculateTax(Price, 18.00) AS GSTAmount,
Price + dbo.CalculateTax(Price, 18.00) AS TotalWithGST
FROM Products;This is something a procedure cannot do. You cannot write SELECT EXEC SomeProcedure — but you can write SELECT SomeFunction(column).
Key Difference 1: Return Values
Procedures optionally return integer status codes and can have multiple OUTPUT parameters:
CREATE PROCEDURE GetOrderStats
@CustomerID INT,
@OrderCount INT OUTPUT,
@TotalSpent DECIMAL(10,2) OUTPUT
AS
BEGIN
SELECT @OrderCount = COUNT(*), @TotalSpent = SUM(TotalAmount)
FROM Orders WHERE CustomerID = @CustomerID;
END;Functions must return exactly one value (scalar) or one table:
-- Scalar function: returns one value
CREATE FUNCTION GetCustomerOrderCount(@CustomerID INT)
RETURNS INT
AS
BEGIN
DECLARE @Count INT;
SELECT @Count = COUNT(*) FROM Orders WHERE CustomerID = @CustomerID;
RETURN @Count;
END;Key Difference 2: Usage in Queries
Functions can be used anywhere an expression is valid:
-- Functions work in SELECT
SELECT Name, dbo.GetCustomerOrderCount(CustomerID) AS Orders FROM Customers;
-- Functions work in WHERE
SELECT * FROM Customers WHERE dbo.GetCustomerOrderCount(CustomerID) > 5;
-- Functions work in ORDER BY
SELECT * FROM Products ORDER BY dbo.CalculateDiscount(Price, Category);Procedures cannot be used in any of these positions. They must be called independently with EXEC or CALL.
Key Difference 3: Data Modification
Procedures can freely modify data:
CREATE PROCEDURE DeactivateInactiveUsers
AS
BEGIN
UPDATE Users SET Status = 'Inactive'
WHERE LastLogin < DATEADD(MONTH, -6, GETDATE());
INSERT INTO AuditLog (Action, ActionDate)
VALUES ('Deactivated inactive users', GETDATE());
END;Functions should not modify data. Most databases enforce this restriction:
-- This would FAIL in most databases
CREATE FUNCTION BadFunction(@ID INT)
RETURNS INT
AS
BEGIN
-- Functions cannot use INSERT, UPDATE, DELETE
UPDATE Table1 SET Col = 'value'; -- ERROR!
RETURN 1;
END;Key Difference 4: Transaction Control
Procedures can manage transactions:
CREATE PROCEDURE SafeTransfer
@From INT, @To INT, @Amount DECIMAL(10,2)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - @Amount WHERE ID = @From;
UPDATE Accounts SET Balance = Balance + @Amount WHERE ID = @To;
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK;
THROW;
END CATCH
END;Functions cannot use BEGIN TRANSACTION, COMMIT, or ROLLBACK.
Types of Functions
Scalar Functions
Return a single value:
CREATE FUNCTION GetAge(@DateOfBirth DATE)
RETURNS INT
AS
BEGIN
RETURN DATEDIFF(YEAR, @DateOfBirth, GETDATE());
END;SELECT Name, dbo.GetAge(DateOfBirth) AS Age FROM Employees;Table-Valued Functions
Return an entire table (like a parameterized view):
CREATE FUNCTION GetDepartmentEmployees(@DeptID INT)
RETURNS TABLE
AS
RETURN (
SELECT EmpID, Name, Salary, JoinDate
FROM Employees
WHERE DepartmentID = @DeptID
);-- Use like a table in FROM clause
SELECT * FROM dbo.GetDepartmentEmployees(3) WHERE Salary > 50000;MySQL Function Syntax
DELIMITER //
CREATE FUNCTION CalculateGrade(marks DECIMAL(5,2))
RETURNS VARCHAR(2)
DETERMINISTIC
BEGIN
DECLARE grade VARCHAR(2);
IF marks >= 90 THEN SET grade = 'A+';
ELSEIF marks >= 80 THEN SET grade = 'A';
ELSEIF marks >= 70 THEN SET grade = 'B';
ELSEIF marks >= 60 THEN SET grade = 'C';
ELSEIF marks >= 50 THEN SET grade = 'D';
ELSE SET grade = 'F';
END IF;
RETURN grade;
END //
DELIMITER ;SELECT StudentName, Marks, CalculateGrade(Marks) AS Grade FROM Results;When to Use a Procedure
Use stored procedures when you need to:
- Perform data modifications (INSERT, UPDATE, DELETE)
- Execute multiple operations as a unit of work
- Control transactions with COMMIT and ROLLBACK
- Return multiple result sets
- Implement complex business logic with error handling
Real-world examples:
- Processing an order (check stock, create order, update inventory, send notification)
- Running a batch job (archive old records, generate reports)
- User registration (create account, assign roles, send welcome email)
When to Use a Function
Use functions when you need to:
- Compute a value based on inputs
- Use the result in a query (SELECT, WHERE, JOIN conditions)
- Encapsulate a calculation that is used in many places
- Create reusable expressions without side effects
Real-world examples:
- Calculating tax, discounts, or totals
- Formatting names or dates
- Computing age from date of birth
- Determining eligibility based on criteria
Real-World Comparison
Imagine an e-commerce system:
-- FUNCTION: Calculate final price (used in queries)
CREATE FUNCTION GetFinalPrice(@Price DECIMAL(10,2), @DiscountPercent INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
RETURN @Price - (@Price * @DiscountPercent / 100);
END;
-- PROCEDURE: Place an order (performs multiple actions)
CREATE PROCEDURE PlaceOrder
@CustomerID INT, @ProductID INT, @Quantity INT
AS
BEGIN
DECLARE @Price DECIMAL(10,2);
SET @Price = (SELECT dbo.GetFinalPrice(Price, Discount) FROM Products WHERE ProductID = @ProductID);
INSERT INTO Orders (CustomerID, ProductID, Quantity, TotalAmount, OrderDate)
VALUES (@CustomerID, @ProductID, @Quantity, @Price * @Quantity, GETDATE());
UPDATE Products SET Stock = Stock - @Quantity WHERE ProductID = @ProductID;
END;The function calculates. The procedure acts. Both are needed, serving different roles.
Common Mistakes to Avoid
Mistake 1: Using a procedure when a function would be cleaner. If you just need to compute a value for a query, a function is more elegant.
Mistake 2: Trying to modify data inside a function. Functions must be side-effect free. Use a procedure for data changes.
Mistake 3: Calling functions in tight loops without considering performance. Scalar functions called per-row can be slow. Consider inline table-valued functions.
Mistake 4: Making functions non-deterministic without marking them. MySQL requires you to specify DETERMINISTIC or NOT DETERMINISTIC.
Best Practices
- Use functions for calculations that need to appear in SELECT or WHERE
- Use procedures for actions that modify data or perform multiple operations
- Keep functions pure — no side effects, same input always gives same output
- Combine both — procedures can call functions for calculations
- Consider performance — inline table-valued functions perform better than scalar functions
- Name clearly — prefix procedures with usp_ and functions with fn_ if your team uses conventions
Understanding the distinction between procedures and functions is essential for writing clean, efficient, and maintainable database code. Each has its place, and using the right tool for the right job makes your SQL professional and performant.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Stored Procedure vs Function in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, stored, procedures, procedure
Related SQL Complete Guide Topics