SQL Server Stored Procedure vs User-Defined Function

The core difference is this: a stored procedure is a routine you execute to do work — it can modify data, run transactions, handle errors, and return one or more result sets — while a user-defined function (UDF) is a routine you call inside a query to compute and return a value, and it cannot change data. Use a procedure for actions and side effects; use a function when you need a value or a set that composes into a SELECT, WHERE, or JOIN. This article shows both in SQL Server 2022, with real CREATE examples and the performance rules that actually matter.

What is a stored procedure?

A stored procedure is a precompiled batch of T-SQL saved in the database and run with EXEC. It is the workhorse for application logic: inserts and updates, multi-step transactions, and returning data to a client. Procedures can accept input and output parameters, return an integer status code, and produce any number of result sets.

Because a procedure is a full program rather than an expression, it can branch with IF, loop, call other procedures, build and run dynamic SQL, and wrap several statements in a single atomic transaction. That freedom is exactly why the engine will not let you embed one inside a query — a procedure may have arbitrary side effects, so it can only be launched deliberately with EXEC. The example below raises an employee's salary and hands the new value back through an output parameter.

CREATE OR ALTER PROCEDURE dbo.usp_AdjustSalary
    @EmployeeId INT,
    @Percent    DECIMAL(5,2),
    @NewSalary  DECIMAL(18,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.Employee
    SET Salary = Salary * (1 + @Percent / 100.0)
    WHERE EmployeeId = @EmployeeId;

    SELECT @NewSalary = Salary
    FROM dbo.Employee
    WHERE EmployeeId = @EmployeeId;
END;

You call it and read the output parameter back:

DECLARE @result DECIMAL(18,2);
EXEC dbo.usp_AdjustSalary @EmployeeId = 42, @Percent = 10, @NewSalary = @result OUTPUT;
SELECT @result AS UpdatedSalary;

What is a user-defined function?

A UDF returns a value and is designed to be embedded in queries. Its whole reason for existing is composability: you write the logic once and reuse it across many queries without copying and pasting the expression. SQL Server has three kinds:

• Scalar function — returns a single value (an INT, DECIMAL, NVARCHAR, etc.).
• Inline table-valued function (iTVF) — returns a table from a single SELECT; the optimizer expands it into the calling query like a parameterized view.
• Multi-statement table-valued function (mTVF) — builds a table variable over several statements; flexible but usually the slowest, so treat it as a last resort.

A scalar function looks like this:

CREATE OR ALTER FUNCTION dbo.fn_FullName
(
    @First NVARCHAR(50),
    @Last  NVARCHAR(50)
)
RETURNS NVARCHAR(101)
AS
BEGIN
    RETURN CONCAT(@First, N' ', @Last);
END;

Because it returns a value, you use it directly inside a query — something a procedure can never do:

SELECT EmployeeId, dbo.fn_FullName(FirstName, LastName) AS FullName
FROM dbo.Employee
WHERE dbo.fn_FullName(FirstName, LastName) LIKE N'A%';

Prefer inline table-valued functions

An inline TVF is the most performant UDF because the optimizer folds its body into the outer query, so it can build a good plan across the whole statement — it behaves like a parameterized view rather than a black box. A multi-statement TVF, by contrast, materializes its results into a table variable that the optimizer must estimate blindly, which frequently leads to poor row estimates and bad join choices. Reach for iTVFs whenever you need reusable, parameterized query logic.

CREATE OR ALTER FUNCTION dbo.fn_EmployeesByDept
(
    @DeptId INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeId, FirstName, LastName, Salary
    FROM dbo.Employee
    WHERE DepartmentId = @DeptId
);

You compose it into queries just like a table:

SELECT e.EmployeeId, e.FirstName, e.Salary
FROM dbo.fn_EmployeesByDept(3) AS e
WHERE e.Salary > 50000
ORDER BY e.Salary DESC;

Where they genuinely differ

The distinctions below are not stylistic — they are enforced by the engine.

• Data modification: procedures can run INSERT, UPDATE, DELETE, and MERGE. Functions cannot modify persistent tables; attempting DML inside a UDF raises an error.
• Use inside a query: functions can appear in SELECT, WHERE, JOIN, and computed columns. A procedure cannot — you call it only with EXEC.
• Transactions: procedures can BEGIN/COMMIT/ROLLBACK TRANSACTION. Functions cannot control transactions.
• Error handling: TRY...CATCH is allowed in procedures but not in functions. A UDF cannot trap errors, so validate inputs before calling it.
• Return shape: procedures return zero or more result sets plus output parameters and an integer status; functions return exactly one thing — a scalar or a table.
• Side effects: procedures may call EXEC, use dynamic SQL, and change session state; functions must stay deterministic and side-effect free (no PRINT, no temp-table side effects on the caller).

Here is the same comparison as a quick reference:

Performance notes for SQL Server 2022

Classic scalar UDFs were notorious performance traps: the engine invoked them once per row, hid their cost from the query plan so the estimated cost looked deceptively cheap, and forced the whole query onto a serial plan even when parallelism would have helped. Over a million-row scan, a single scalar UDF call could dominate runtime. SQL Server 2019 introduced scalar UDF inlining (part of Intelligent Query Processing), and it carries through to SQL Server 2022 — eligible scalar functions are transformed into equivalent relational expressions and folded into the plan, often turning a row-by-row call into a set-based operation with no code change. Not every function qualifies; time-dependent functions, those that reference table variables, and certain other constructs opt the function out, which is why you should confirm eligibility rather than assume it.

Practical guidance:

1. Prefer inline table-valued functions over scalar UDFs and multi-statement TVFs when you can express the logic as a single SELECT.
2. Keep scalar UDFs simple so they qualify for inlining; check the actual plan and sys.sql_modules.is_inlineable.
3. If a scalar UDF is not inlineable and sits in a hot path over many rows, rewrite it as an iTVF used with CROSS APPLY.
4. Put data-changing, multi-step, transactional work in procedures — that is what they are built for.

When should you use which?

Use a stored procedure when the operation does something: writes data, coordinates a transaction, orchestrates several steps, needs TRY...CATCH, or returns multiple result sets to an application. It is also the natural home for anything an application calls as a command — "place this order", "close this ticket", "run this nightly job". Use a function when you need a reusable value or set that plugs into queries — formatting, calculations, or parameterized filters — and especially an inline TVF for query-composable logic that you would otherwise duplicate across many statements. If you are ever tempted to change data inside a function, that is the signal you actually want a procedure. And when a scalar function starts showing up in the slow queries of a hot workload, revisit it: confirm it is inlineable, or reshape it into an inline table-valued function invoked with CROSS APPLY. Getting this choice right keeps both your data safe and your query plans fast.

Key takeaways

• Procedures perform actions and side effects; functions compute and return values.
• Only functions can be used inside SELECT, WHERE, and JOIN; procedures run via EXEC.
• Functions cannot modify data, manage transactions, or use TRY...CATCH.
• Inline table-valued functions are the most optimizer-friendly UDF — prefer them.
• SQL Server 2019+ and 2022 can inline eligible scalar UDFs, but keep them simple to qualify.
• Choose based on intent: side effects and transactions mean a procedure; a reusable value or set means a function.

Frequently asked questions

Can a user-defined function modify data in SQL Server?

No. A UDF cannot run INSERT, UPDATE, DELETE, or MERGE against persistent tables, and it cannot manage transactions. If you need to change data, use a stored procedure.

Can I call a stored procedure inside a SELECT statement?

No. Procedures are invoked with EXEC and cannot be embedded in SELECT, WHERE, or JOIN. If you need query-composable logic, use an inline table-valued function instead.

Are scalar UDFs still slow in SQL Server 2022?

Not necessarily. SQL Server 2019 added scalar UDF inlining, which continues in SQL Server 2022 and folds eligible scalar functions into the query plan. Keep the function simple so it qualifies, and prefer inline TVFs for set-based logic.

Does a stored procedure or a function support TRY...CATCH?

Only a stored procedure. Structured error handling with TRY...CATCH is not permitted inside a user-defined function, so validate inputs before you call one.