Stored Procedure vs Function in SQL Server: Differences I Tested

Use a function when you need a value or rows inside SELECT, WHERE or JOIN. Use a stored procedure when the code changes data, needs a transaction or TRY…CATCH, or returns OUTPUT parameters.

That’s my rule of thumb for stored procedure vs function in SQL Server, and speed doesn’t change it. On 100,000 orders an inlined scalar UDF took 0.6 s; with inlining off, 9.1 s.

Stored Procedure vs Function in SQL Server at a Glance

Feature	Stored procedure	Function (UDF)
Returns	Result sets, OUTPUT params, RETURN code	One value or one table
Inside SELECT / WHERE / JOIN	No, run it with EXEC	Yes
INSERT / UPDATE / DELETE	Yes	Msg 443 (table variables only)
Transactions, TRY…CATCH	Yes	Msg 443
Calls a procedure	Yes	Msg 557
Temp tables	Yes	Msg 2772
Dynamic SQL	Yes	Msg 443
100,000 rows, my run	0.2 s	0.6 s inlined, 9.1 s not

How I Tested Each Function and Procedure

Tested on SQL Server 2025 (17.0.1000.7) Developer, compatibility level 170, sqlcmd in VS Code. Five state tax rates and 100,000 orders:

CREATE TABLE dbo.TaxRates (State char(2) PRIMARY KEY, Rate decimal(5,4));
INSERT INTO dbo.TaxRates VALUES
('TX', 0.0625), ('CA', 0.0725), ('NY', 0.0400), ('FL', 0.0600), ('WA', 0.0650);

CREATE TABLE dbo.Orders (OrderId int IDENTITY PRIMARY KEY,
                         State char(2), Amount decimal(10,2));
INSERT INTO dbo.Orders (State, Amount)
SELECT CHOOSE(n % 5 + 1, 'TX', 'CA', 'NY', 'FL', 'WA'), 20 + n % 480
FROM (SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b) AS nums;

CREATE TABLE dbo.OrderLog (LogId int IDENTITY, Note varchar(30));

The four objects I compared. New to the syntax? See how to create functions in SQL Server.

CREATE OR ALTER FUNCTION dbo.fn_TaxRate (@State char(2))
RETURNS decimal(5,4)
AS
BEGIN
    RETURN (SELECT Rate FROM dbo.TaxRates WHERE State = @State);
END;
GO
CREATE OR ALTER FUNCTION dbo.tvf_TaxRate (@State char(2))
RETURNS TABLE
AS
RETURN (SELECT Rate FROM dbo.TaxRates WHERE State = @State);
GO
CREATE OR ALTER PROCEDURE dbo.usp_LogOrder
    @Note varchar(30), @LogId int OUTPUT
AS
INSERT INTO dbo.OrderLog (Note) VALUES (@Note);
SET @LogId = SCOPE_IDENTITY();
RETURN 0;
GO
CREATE OR ALTER PROCEDURE dbo.usp_TaxTotal @Total decimal(14,2) OUTPUT
AS
SELECT @Total = SUM(o.Amount * r.Rate)
FROM dbo.Orders AS o JOIN dbo.TaxRates AS r ON r.State = o.State;

Calling a Function vs Executing a Stored Procedure

Same orders table: the function sits inside the query. The procedure needs its own EXEC and answers through an OUTPUT parameter and a return code:

SET NOCOUNT ON;
SELECT TOP (2) OrderId, State, dbo.fn_TaxRate(State) AS Rate
FROM dbo.Orders
WHERE dbo.fn_TaxRate(State) > 0.07;

SELECT TOP (2) o.OrderId, r.Rate
FROM dbo.Orders AS o CROSS APPLY dbo.tvf_TaxRate(o.State) AS r;

DECLARE @id int, @rc int;
EXEC @rc = dbo.usp_LogOrder @Note = 'Austin restock', @LogId = @id OUTPUT;
SELECT @id AS NewLogId, @rc AS ReturnCode;

Output:

OrderId     State Rate   
----------- ----- -------
          1 CA      .0725
          6 CA      .0725
OrderId     Rate   
----------- -------
          1   .0725
          2   .0400
NewLogId    ReturnCode 
----------- -----------
          1           0

CROSS APPLY is how an inline function that returns a table (TVF) joins in. SELECT * FROM dbo.usp_LogOrder fails: a procedure isn’t a table source.

What a SQL Server Function Can’t Do: Msg 443, 2772 and 557

I fed six procedure-style statements into a test function and let TRY…CATCH print each error:

SET NOCOUNT ON;
DECLARE @b TABLE (Id int IDENTITY, Body nvarchar(80));
INSERT @b VALUES (N'INSERT dbo.OrderLog (Note) VALUES (''x'');'),
  (N'BEGIN TRAN; SET @n = 1; COMMIT;'),
  (N'BEGIN TRY SET @n = 1; END TRY BEGIN CATCH END CATCH;'),
  (N'SELECT @n = COUNT(*) FROM #Recent;'),
  (N'EXEC (N''SELECT 1'');'),
  (N'EXEC dbo.usp_LogOrder ''x'', @n OUTPUT;');
DECLARE @i int = 1, @n int, @sql nvarchar(300);
WHILE @i <= 6
BEGIN
  SELECT @sql = N'CREATE OR ALTER FUNCTION dbo.fn_Try() RETURNS int AS BEGIN '
    + N'DECLARE @n int = 0; ' + Body + N' RETURN @n; END' FROM @b WHERE Id = @i;
  BEGIN TRY EXEC (@sql); SET @n = dbo.fn_Try(); END TRY
  BEGIN CATCH PRINT CONCAT(@i, ') Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;
  SET @i += 1;
END;
DROP FUNCTION IF EXISTS dbo.fn_Try;

Six attempts, six errors:

1) Msg 443: Invalid use of a side-effecting operator 'INSERT' within a function.
2) Msg 443: Invalid use of a side-effecting operator 'BEGIN TRANSACTION' within a function.
3) Msg 443: Invalid use of a side-effecting operator 'BEGIN TRY' within a function.
4) Msg 2772: Cannot access temporary tables from within a function.
5) Msg 443: Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
6) Msg 557: Only functions and some extended stored procedures can be executed from within a function.

Five failed at CREATE. The procedure call compiled, then failed when the function ran, so Msg 557 waits for your first query.

All six work in a procedure, which is why TRY…CATCH and temp tables in a procedure are everyday patterns.

Is a Stored Procedure Faster Than a Function?

You’ll read that procedures win because they’re precompiled when you create a stored procedure. They compile at first execution, like functions. Same orders and tax math, timed with SYSDATETIME() over 3 runs:

SET NOCOUNT ON;
DECLARE @r int = 1, @t datetime2, @x decimal(14,2);
DECLARE @s TABLE (v decimal(14,2));
DECLARE @ms TABLE (Method varchar(26), Ms int);
WHILE @r <= 3
BEGIN
  SET @t = SYSDATETIME();
  INSERT @s SELECT SUM(Amount * dbo.fn_TaxRate(State)) FROM dbo.Orders;
  INSERT @ms VALUES ('Scalar UDF, inlined', DATEDIFF(ms, @t, SYSDATETIME()));
  SET @t = SYSDATETIME();
  INSERT @s SELECT SUM(Amount * dbo.fn_TaxRate(State)) FROM dbo.Orders
    OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));
  INSERT @ms VALUES ('Scalar UDF, inlining off', DATEDIFF(ms, @t, SYSDATETIME()));
  SET @t = SYSDATETIME();
  INSERT @s SELECT SUM(o.Amount * r.Rate)
    FROM dbo.Orders AS o CROSS APPLY dbo.tvf_TaxRate(o.State) AS r;
  INSERT @ms VALUES ('Inline TVF', DATEDIFF(ms, @t, SYSDATETIME()));
  SET @t = SYSDATETIME();
  EXEC dbo.usp_TaxTotal @x OUTPUT;
  INSERT @ms VALUES ('Stored procedure', DATEDIFF(ms, @t, SYSDATETIME()));
  SET @r += 1;
END;
SELECT Method, AVG(Ms) AS AvgMs FROM @ms GROUP BY Method ORDER BY AvgMs;

My timings:

Method                     AvgMs      
-------------------------- -----------
Stored procedure                   156
Inline TVF                         195
Scalar UDF, inlined                635
Scalar UDF, inlining off          9120

The procedure (0.2 s) and inline TVF (0.2 s) were close; the inlined UDF took 0.6 s. Turning inlining off cost 9.1 s.

Note: other workloads share my instance, so timings swing between runs.

Where the Answer Flips: Scalar UDFs That Can’t Be Inlined

Inlining needs SQL Server 2019+ and compatibility level 150 or higher. GETDATE(), a table variable, a CTE or more than one RETURN blocks it, and sys.sql_modules.is_inlineable shows 0.

Then a scalar UDF runs row by row like my ‘inlining off’ test, and the procedure or an inline TVF wins.

When to Use a Function Instead of a Stored Procedure

• Reusable filter, calculated column or join source: inline TVF, or an inlineable scalar UDF.
• Writes, transactions, TRY…CATCH, temp tables: stored procedure.
• One value back to an app: an OUTPUT parameter or a return value.

My rule of thumb: functions read and compute, procedures act. Re-test scalar UDFs after an upgrade.

Next step: pass inputs with stored procedure parameters. On Postgres, create a stored procedure in PostgreSQL.

• Execute a function with parameters
• On MariaDB: MariaDB CREATE FUNCTION

Frequently Asked Questions