Difference between Stored Procedure and Function in SQL Server
Estimated study time: 6 minutes. Know which one fits your use case.
Stored procedures and user-defined functions can both encapsulate reusable SQL logic, but SQL Server enforces some hard rules that separate them.
Basic Syntax
-- Stored Procedure
CREATE PROCEDURE GetEmployeesByDept (@Dept VARCHAR(50))
AS
BEGIN
SELECT * FROM Employees WHERE Department = @Dept;
END;
-- Scalar Function
CREATE FUNCTION GetEmployeeCount (@Dept VARCHAR(50))
RETURNS INT
AS
BEGIN
DECLARE @Count INT;
SELECT @Count = COUNT(*) FROM Employees WHERE Department = @Dept;
RETURN @Count;
END;
Key Differences
| Aspect | Stored Procedure | Function |
|---|---|---|
| Return value | Optional; zero, one, or multiple result sets | Mandatory - a scalar value or a table |
| Used in SELECT | No, must be called with EXEC | Yes, can be used inline in SELECT, WHERE, JOIN |
| Parameters | Input and output parameters | Input parameters only |
| DML statements | Can INSERT, UPDATE, DELETE on tables | Cannot modify tables (except table variables inside the function) |
| Transaction control | Can use COMMIT / ROLLBACK | Cannot manage transactions |
| Error handling | Supports TRY...CATCH | Very limited error handling |
| Calling other routines | Can call other procedures and functions | Can call other functions, but not stored procedures |
When to Use Which
Reach for a stored procedure when you need to perform data changes, manage transactions, or return multiple result sets - think batch jobs, report generation, or multi-step business logic.
Reach for a function when you need a reusable calculation that plugs directly into a query, such as a computed column, a filter condition, or a value used inside a SELECT list.
-- A function can be used directly inside a query: SELECT Name, dbo.GetEmployeeCount(Department) AS TeamSize FROM Employees; -- A procedure cannot - it must be executed separately: EXEC GetEmployeesByDept @Dept = 'Sales';
💡 Tip: If you find yourself wanting a function to also update a table, that's usually a sign you actually need a stored procedure.