SQL Server Stored Procedure vs User-Defined Function

Stored procedures and user-defined functions both package T-SQL for reuse, and choosing between them trips up every SQL Server newcomer. The short version: functions compute inside queries; procedures do work. This tutorial builds one of each on the same data, shows what each can and cannot do, and ends with a decision table you can apply mechanically.

A stored procedure — action with side effects

CREATE PROCEDURE dbo.AddOrder
    @CustomerId INT,
    @Amount     DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Orders (CustomerId, OrderDate, Amount)
    VALUES (@CustomerId, CAST(GETDATE() AS date), @Amount);

    SELECT SCOPE_IDENTITY() AS NewOrderId;
END;

EXEC dbo.AddOrder @CustomerId = 4, @Amount = 150.00;

NewOrderId
----------
8

Everything in that little procedure is illegal in a function: the INSERT (side effect), GETDATE()-driven data change, returning a result set from arbitrary statements. Procedures are the unit of doing: modify data, run in transactions, call other procedures, return multiple result sets, use output parameters, handle errors with TRY/CATCH.

A function — computation inside queries

CREATE FUNCTION dbo.GetCustomerTotal (@CustomerId INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN (SELECT ISNULL(SUM(Amount), 0)
            FROM Orders WHERE CustomerId = @CustomerId);
END;

SELECT Name, dbo.GetCustomerTotal(CustomerId) AS LifetimeValue
FROM Customers ORDER BY LifetimeValue DESC;

Name   LifetimeValue
-----  -------------
Rahul  2300.00
Meera  2090.00
Asha   1650.00
John   0.00
Sara   0.00

And everything that does is illegal-or-awkward for a procedure: you can't SELECT dbo.SomeProc(...) in a query, join a procedure, or use one in a WHERE clause. Functions are the unit of computing within a SELECT — the three function kinds and their performance characters have their own post.

The capability matrix

The "call the other kind" row explains a common design: procedures orchestrate (validate → write → log), calling functions for the calculations along the way. Never the reverse.

Where each wins in practice

Procedures for the write path. An AddOrder-style procedure gives you one audited entry point for a business operation — validation, the write, and error handling in one place, executable by an app (EF Core's FromSql/ExecuteSql, Dapper) or a job. If you follow the repository pattern, procedures sit comfortably behind the repository interface.

Inline table-valued functions for reusable read shapes. A filter or projection you need in many queries — "active products with computed margins" — as an inline TVF behaves like a parameterized view, composes with further WHERE/JOIN, and optimizes as if you'd pasted the SQL inline. A procedure returning the same rows can't be composed at all: you'd INSERT #temp EXEC ... and lose the optimizer's help.

Scalar functions sparingly. Formatting and pure math, yes. Data-touching scalar UDFs execute per row unless SQL Server 2019+ manages to inline them — a per-row hidden query is the classic "why is this report slow" answer.

Performance notes that actually matter

• Both compile to cached plans. Procedures get parameter sniffing: the first call's parameter values shape the plan. Usually good; occasionally terrible (a plan built for a rare parameter reused for a common one). Fixes range from OPTION (RECOMPILE) to OPTIMIZE FOR.
• SET NOCOUNT ON in procedures suppresses the chatty "(1 row affected)" messages — a micro-optimization for high-frequency calls and a courtesy to client libraries.
• A procedure's result can't be joined; a TVF's can. This single fact should settle most "read logic" placement debates in favor of TVFs.

The mechanical decision

1. Does it modify data, need a transaction, or orchestrate steps? → procedure
2. Do queries need to call it inline, join it, or filter by it? → function
3. Is it a reusable query (returns rows)? → inline table-valued function
4. Is it a reusable action (does things)? → procedure

If the honest answer to 1 and 2 is "both", split it: the computation as a function, the action as a procedure that uses it.