What is the difference between procedure and function?
In SQL, both procedures and functions are named blocks of code designed to perform specific tasks. While they share similarities, they have distinct characteristics regarding their return values, DML/DDL operations, and how they are called.
SQL Procedure
A SQL stored procedure is a precompiled collection of one or more SQL statements or commands that are stored in the database and can be executed multiple times. Procedures can take input parameters and return output parameters or result sets.
- Can perform DML (INSERT, UPDATE, DELETE) and DDL (CREATE, ALTER, DROP) operations.
- Can have IN, OUT, and INOUT parameters.
- Can return multiple values using OUT parameters or result sets.
- Cannot be directly used in SQL queries (e.g., in a SELECT statement).
- Executed using the EXEC or CALL command.
- Are often used for complex business logic, data modification, and administrative tasks.
- Do not necessarily return a value.
- Can include transaction control statements.
SQL Function
A SQL function is a named block of code that accepts input parameters, performs a calculation or action, and always returns a single value. Functions are primarily used for computations and manipulating data.
- Generally cannot perform DML or DDL operations (except for specific table-valued functions in some RDBMS, but generally they are read-only).
- Can have IN parameters only.
- Always returns a single value (scalar or table).
- Can be used directly in SQL queries (e.g., in SELECT, WHERE, HAVING clauses).
- Executed by being called in a query or an EXEC statement (in some contexts).
- Are often used for calculations, data validation, and returning a computed value.
- Must return a value.
- Cannot include transaction control statements.
Key Differences
| Feature | Procedure | Function |
|---|---|---|
| Return Value | Optional (can return multiple result sets or OUT parameters) | Mandatory (always returns a single scalar or table value) |
| DML/DDL | Allowed | Generally not allowed (read-only) |
| Parameters | IN, OUT, INOUT | IN only |
| Usage | Cannot be directly called in SQL statements (e.g., SELECT) | Can be directly called in SQL statements (e.g., SELECT, WHERE) |
| Execution | EXEC or CALL command | Called as part of an expression or query |
| Transaction | Can manage transactions (COMMIT, ROLLBACK) | Cannot manage transactions |
When to Use Which?
Use procedures when you need to perform complex business logic, manipulate data (INSERT, UPDATE, DELETE), manage transactions, or return multiple result sets. Use functions when you need to compute a value, perform calculations, or validate data within a query, and always expect a single return value.
Example
Procedure Example
CREATE PROCEDURE GetEmployeeCountByDepartment
@DepartmentName NVARCHAR(50),
@EmployeeCount INT OUTPUT
AS
BEGIN
SELECT @EmployeeCount = COUNT(*)
FROM Employees
WHERE Department = @DepartmentName;
END;
-- To execute:
-- DECLARE @Count INT;
-- EXEC GetEmployeeCountByDepartment 'Sales', @Count OUTPUT;
-- SELECT @Count AS SalesEmployees;Function Example
CREATE FUNCTION CalculateTax(@Amount DECIMAL(10, 2), @TaxRate DECIMAL(5, 2))
RETURNS DECIMAL(10, 2)
AS
BEGIN
RETURN @Amount * @TaxRate;
END;
-- To use in a query:
-- SELECT ProductName, Price, dbo.CalculateTax(Price, 0.08) AS SalesTax
-- FROM Products;