SQL Server › Stored Procedures & Functions

What are the main differences between a stored procedure and a function in terms of what each one can and cannot do?

Intermediate

Answer

A stored procedure can perform data modifications like INSERT, UPDATE, and DELETE, can have output parameters, and cannot be used directly inside a SELECT statement, while a function must return a value, generally cannot modify data in the database, but can be used directly within a SELECT statement or a WHERE clause, making them suited for different kinds of tasks.

-- A function cannot modify data
CREATE FUNCTION CalculateDiscount (@Amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS BEGIN RETURN @Amount * 0.9; END;

-- A procedure can modify data
CREATE PROCEDURE ApplyDiscount @OrderId INT
AS BEGIN UPDATE Orders SET Amount = dbo.CalculateDiscount(Amount) WHERE OrderId = @OrderId; END;

Real-world example A pricing system uses a function to calculate a discounted price that can be directly used inside reporting queries, while using a separate stored procedure to actually apply and save that discount to a specific order in the database.

Common follow-ups: Why can't a function be used to modify data directly?;When would you choose a stored procedure over a function for a specific task?

User Defined Functions;Query Optimization & Plans