Stored procedure vs function in SQL Server: differences and when to use each

Both are named T-SQL code saved in the database, and both take parameters. The difference is where you can use them. A stored procedure is a command: you EXEC it, and it can do almost anything. A function is an expression: you use it inside a query, and in return it promises not to change anything.

That one difference explains the rest of the list. The performance differences between the three kinds of function matter even more, and they are measured below against the SpDevDemo database.

The differences

	Stored procedure	Function
How it’s called	EXEC dbo.Proc	Inside a statement: SELECT dbo.Fn(x), FROM dbo.Fn(x)
Changes data (INSERT, UPDATE, DELETE on tables)	Yes	No
Returns	Result sets, OUTPUT parameters, an integer status	One value, or one table
Usable in SELECT, WHERE, JOIN	No	Yes
Transactions, TRY…CATCH	Yes	No
Temp tables, dynamic SQL	Yes	No (table variables only)
Calls procedures	Yes	No
Non-deterministic built-ins (NEWID(), RAND())	Yes	Mostly no

If the code needs to change data, run in a transaction or handle errors, it’s a procedure. If you need its result inside a query, it’s a function.

Three kinds of function

SQL Server has three, and they perform very differently:

-- Scalar: returns one value
CREATE OR ALTER FUNCTION dbo.OrderTotalWithTax (@Total DECIMAL(12, 2), @Country CHAR(2))
RETURNS DECIMAL(12, 2)
AS
BEGIN
    RETURN @Total * CASE @Country WHEN 'GB' THEN 1.20 WHEN 'DE' THEN 1.19 WHEN 'PK' THEN 1.18 ELSE 1.00 END;
END
GO

-- Inline table-valued: a single SELECT, like a view with parameters
CREATE OR ALTER FUNCTION dbo.CustomerOrders (@CustomerId INT)
RETURNS TABLE
AS
RETURN
    SELECT OrderId, OrderDate, Total
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
GO

-- Multi-statement table-valued: fills a table variable step by step
CREATE OR ALTER FUNCTION dbo.CustomerOrdersMulti (@CustomerId INT)
RETURNS @result TABLE (OrderId INT, OrderDate DATE, Total DECIMAL(12, 2))
AS
BEGIN
    INSERT @result SELECT OrderId, OrderDate, Total FROM dbo.Orders WHERE CustomerId = @CustomerId;
    RETURN;
END

Inline table-valued functions are free

An inline function is expanded into the query that uses it, like a view. The optimizer sees the real tables and indexes, so it costs nothing to use one. If you need a reusable query with parameters, this is the best tool in T-SQL.

Multi-statement functions hide their rows

A multi-statement function returns a table variable. Before SQL Server 2017, the optimizer guessed that it returns 100 rows (1 row before SQL Server 2014), whatever it really returns. Since SQL Server 2017, at compatibility level 140 or higher, interleaved execution runs the function first and uses its real row count, in read-only queries that use it (not, for example, inside an INSERT, UPDATE or DELETE, or on the inner side of a CROSS APPLY). It still can’t use indexes or statistics on the function’s output. Prefer an inline function whenever the logic fits in one SELECT.

Scalar functions used to run once per row

A scalar function in a SELECT over 200,000 rows is called 200,000 times. Before SQL Server 2019, each call was a separate execution, it couldn’t run in parallel, and the plan showed none of its cost. SQL Server 2019 added scalar UDF inlining (compatibility level 150 and higher): when a function qualifies, SQL Server rewrites it into the query as an expression.

Compare the same function with inlining on and off:

CREATE OR ALTER FUNCTION dbo.OrderTotalWithTaxNoInline (@Total DECIMAL(12, 2), @Country CHAR(2))
RETURNS DECIMAL(12, 2)
WITH INLINE = OFF
AS
BEGIN
    RETURN @Total * CASE @Country WHEN 'GB' THEN 1.20 WHEN 'DE' THEN 1.19 WHEN 'PK' THEN 1.18 ELSE 1.00 END;
END
GO

SET STATISTICS TIME ON;

SELECT SUM(dbo.OrderTotalWithTax(o.Total, c.Country))
FROM dbo.Orders AS o JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId;

SELECT SUM(dbo.OrderTotalWithTaxNoInline(o.Total, c.Country))
FROM dbo.Orders AS o JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId;

SET STATISTICS TIME OFF;

On SQL Server 2022:

-- inlined
 SQL Server Execution Times: CPU time = 54 ms,  elapsed time = 60 ms.
-- not inlined
 SQL Server Execution Times: CPU time = 1619 ms,  elapsed time = 1925 ms.

Same result, about thirty times less CPU. The non-inlined function ran 200,000 times, once per order; the inlined one became part of the query’s arithmetic. Not every function can be inlined: functions that use time-dependent built-ins like GETDATE(), table variables, or certain other constructs are excluded. Check yours:

SELECT OBJECT_NAME(object_id) AS function_name, is_inlineable, inline_type
FROM sys.sql_modules
WHERE OBJECT_NAME(object_id) LIKE 'OrderTotalWithTax%';

is_inlineable = 1 means SQL Server can inline it; inline_type = 1 means inlining is switched on for it. A database below compatibility level 150 never inlines.

Calling a procedure where you need a function

You can’t SELECT ... FROM dbo.SomeProcedure. The options:

• Rewrite the procedure’s query as an inline table-valued function and have the procedure call it. Both callers then share one definition.
• Capture its rows with INSERT INTO #t EXEC dbo.SomeProcedure (see executing procedures for the limits).
• OPENROWSET or OPENQUERY against the local server work but need ad hoc distributed queries enabled. Avoid them.

Which one to use

• Changes data, needs a transaction or error handling: stored procedure.
• A reusable query with parameters, used in joins: inline table-valued function.
• A calculation on a few values, used in queries: a scalar function, if it can be inlined; otherwise write the expression in the query, or put it in an inline table-valued function and CROSS APPLY it.
• Multi-step logic that returns a table: a stored procedure that fills a temp table, or a multi-statement function if it really must be usable in a FROM clause.

SpDev Pro flags the scalar functions that cost the most in a profiled procedure: when a function wasn’t inlined and runs once per row, the finding shows how many times it ran and how much of the statement’s time it took.