How to Execute a Stored Procedure in SQL Server (EXEC With Parameters)

Many guides still execute stored procedures in SQL Server with bare values and no schema, like EXEC usp_OrdersByState 'TX', 100. It works, right up until someone adds a parameter.

The safer way is EXEC plus the schema-qualified name and named parameters, so every value lands where you meant it: EXEC dbo.usp_OrdersByState @State = 'TX', @MinTotal = 100;

In this tutorial, I’ll answer the “how to execute stored procedure in SQL Server” question for every case I hit on SQL Server 2025, from OUTPUT values to procedures that UPDATE rows.

The Sample Orders Table for the Stored Procedure Examples

I’ll use one small dbo.Orders table throughout, in a demo database called DfqExecProc, plus one procedure in a reporting schema:

CREATE TABLE dbo.Orders (
    OrderID   int           PRIMARY KEY,
    Customer  nvarchar(20)  NOT NULL,
    State     char(2)       NOT NULL,
    OrderDate date          NOT NULL,
    Total     decimal(10,2) NOT NULL,
    Status    nvarchar(10)  NOT NULL
);
INSERT INTO dbo.Orders (OrderID, Customer, State, OrderDate, Total, Status) VALUES
(1001, N'Megan Bowen',     'WA', '2026-09-02',  245.50, N'Shipped'),
(1002, N'Alex Wilber',     'TX', '2026-09-05',   89.99, N'Shipped'),
(1003, N'Adele Vance',     'CA', '2026-09-14', 1210.00, N'Shipped'),
(1004, N'Diego Siciliani', 'TX', '2026-09-21',  432.75, N'Pending'),
(1005, N'Lee Gu',          'NY', '2026-10-01',   67.20, N'Pending'),
(1006, N'Nestor Wilke',    'TX', '2026-10-08',  158.00, N'Pending'),
(1007, N'Lynne Robbins',   'WA', '2026-10-12',  990.40, N'Pending'),
(1008, N'Isaiah Langer',   'IL', '2026-10-19',  312.15, N'Pending');
GO
CREATE SCHEMA reporting;
GO
CREATE PROCEDURE reporting.usp_StateSummary
AS
    SELECT State, COUNT(*) AS Orders, SUM(Total) AS Revenue
    FROM dbo.Orders
    GROUP BY State;

Watch the three Texas orders and the five Pending ones. New to procedures? Start with how to create a stored procedure in SQL Server.

EXEC vs EXECUTE: The Stored Procedure Syntax

EXEC and EXECUTE are the same keyword. Here’s the statement, trimmed to the parts you’ll use:

[ { EXEC | EXECUTE } ]
    [ @return_status = ] schema_name.procedure_name
    [ [ @parameter = ] { value | @variable [ OUTPUT ] | DEFAULT } ] [ ,...n ]
    [ WITH RECOMPILE | WITH RESULT SETS ( ... ) ] ;

You can drop the keyword only for a batch’s first statement, so I always write it, with the schema. Our stored procedure naming convention guide explains why your own names shouldn’t start with sp_.

Method 1: Execute a Stored Procedure Without Parameters (Recommended Start)

With no input, the call is just EXEC and the name. Here’s the procedure and the call in one script:

CREATE OR ALTER PROCEDURE dbo.usp_GetOrders
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, Customer, State, Total
    FROM dbo.Orders
    ORDER BY OrderID;
END;
GO

EXEC dbo.usp_GetOrders;

Output:

OrderID     Customer             State Total       
----------- -------------------- ----- ------------
       1001 Megan Bowen          WA          245.50
       1002 Alex Wilber          TX           89.99
       1003 Adele Vance          CA         1210.00
       1004 Diego Siciliani      TX          432.75
       1005 Lee Gu               NY           67.20
       1006 Nestor Wilke         TX          158.00
       1007 Lynne Robbins        WA          990.40
       1008 Isaiah Langer        IL          312.15

How does this work? GO ends the batch that creates the procedure, because CREATE PROCEDURE must sit alone in its batch. The next batch runs it and gets all eight rows.

Not sure what a procedure does? View the stored procedure definition before you run it.

Method 2: Exec Stored Procedure With Parameters (Positional vs Named)

Pass values by position, in declared order, or by name with @parameter = value in any order. Same orders table, now filtered by state and a minimum total:

CREATE OR ALTER PROCEDURE dbo.usp_OrdersByState
    @State    char(2),
    @MinTotal decimal(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, Customer, Total
    FROM dbo.Orders
    WHERE State = @State AND Total >= @MinTotal;
END;
GO

EXEC dbo.usp_OrdersByState 'TX', 100;
EXEC dbo.usp_OrdersByState @MinTotal = 100, @State = 'TX';

Output:

OrderID     Customer             Total       
----------- -------------------- ------------
       1004 Diego Siciliani            432.75
       1006 Nestor Wilke               158.00
OrderID     Customer             Total       
----------- -------------------- ------------
       1004 Diego Siciliani            432.75
       1006 Nestor Wilke               158.00

How does this work? Both calls return orders 1004 and 1006; Alex Wilber’s $89.99 order misses the $100 minimum. The positional call maps values by order, while the named call states each mapping.

I use named parameters in anything that lives longer than a test. If someone adds a parameter in the middle, positional calls silently shift every value. Important: once you name one, name the rest:

EXEC dbo.usp_OrdersByState @State = 'TX', 100;

SQL Server rejects that line with error 119 before anything runs: “Must pass parameter number 2 and subsequent parameters as ‘@name = value’.”

For declaring types and sizes, see our guide to SQL Server stored procedure parameters.

Method 3: Execute a Stored Procedure With Multiple Parameters and Dates

Multiple parameters are just separated by commas. This one takes a date range and an optional state:

CREATE OR ALTER PROCEDURE dbo.usp_OrdersBetween
    @FromDate date,
    @ToDate   date,
    @State    char(2) = NULL      -- optional
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, Customer, State, OrderDate
    FROM dbo.Orders
    WHERE OrderDate BETWEEN @FromDate AND @ToDate
      AND (@State IS NULL OR State = @State);
END;
GO

EXEC dbo.usp_OrdersBetween '2026-09-01', '2026-09-30', 'TX';

Output:

OrderID     Customer             State OrderDate       
----------- -------------------- ----- ----------------
       1002 Alex Wilber          TX          2026-09-05
       1004 Diego Siciliani      TX          2026-09-21

How does this work? SQL Server converts '2026-09-01' to the date parameter and returns orders 1002 (09/05/2026) and 1004 (09/21/2026). I use YYYY-MM-DD because it reads the same under every language setting.

Important: EXEC dbo.usp_OrdersBetween GETDATE(), ... fails with Incorrect syntax near ')'. Put the function result in a variable and pass that. Here are the SQL Server date and time functions you can use.

Method 4: Use Default and Optional Parameters

@State char(2) = NULL has a default, so you can leave it out or pass DEFAULT. A parameter without one must always be supplied:

-- @State is optional
EXEC dbo.usp_OrdersBetween '2026-10-01', '2026-10-31';

-- @FromDate has no default
EXEC dbo.usp_OrdersBetween @ToDate = '2026-10-31';

Output:

OrderID     Customer             State OrderDate       
----------- -------------------- ----- ----------------
       1005 Lee Gu               NY          2026-10-01
       1006 Nestor Wilke         TX          2026-10-08
       1007 Lynne Robbins        WA          2026-10-12
       1008 Isaiah Langer        IL          2026-10-19
HResult 0xC9, Level 16, State 4
Procedure or function 'usp_OrdersBetween' expects parameter '@FromDate', which was not supplied.

How does this work? The first call skips @State, so it stays NULL and all four October orders come back. The second skips @FromDate, which has no default, so SQL Server refuses.

Pass the value, or alter the stored procedure to give the parameter a default.

Method 5: Get Values Back With OUTPUT Parameters

An OUTPUT parameter hands a value back. You need a variable to receive it, and OUTPUT in the call as well as in the procedure:

CREATE OR ALTER PROCEDURE dbo.usp_StateSales
    @State      char(2),
    @OrderCount int           OUTPUT,
    @Revenue    decimal(12,2) OUTPUT
AS
    SELECT @OrderCount = COUNT(*), @Revenue = SUM(Total)
    FROM dbo.Orders WHERE State = @State;
GO

SET NOCOUNT ON;
DECLARE @n int, @rev decimal(12,2);
EXEC dbo.usp_StateSales 'TX', @n OUTPUT, @rev OUTPUT;
SELECT @n AS TxOrders, @rev AS TxRevenue;

EXEC dbo.usp_StateSales 'WA', @n, @rev;   -- OUTPUT missing
SELECT @n AS WaOrders, @rev AS WaRevenue;

Output:

TxOrders    TxRevenue     
----------- --------------
          3         680.74
WaOrders    WaRevenue     
----------- --------------
          3         680.74

How does this work? Texas has 3 orders worth $680.74, copied back into @n and @rev. The Washington call left out OUTPUT, so the variables kept the Texas numbers and nothing warned me.

More patterns are in our SQL Server stored procedure OUTPUT parameter tutorial.

Method 6: Execute a Stored Procedure That Updates Rows

Update one value and check @@ROWCOUNT

This procedure sets one order’s status, saves @@ROWCOUNT right after the UPDATE, and returns it as an OUTPUT parameter:

CREATE OR ALTER PROCEDURE dbo.usp_SetOrderStatus
    @OrderID int, @Status nvarchar(10),
    @RowsUpdated int = 0 OUTPUT
AS
    SET NOCOUNT ON;
    UPDATE dbo.Orders SET Status = @Status
    WHERE OrderID = @OrderID;
    SET @RowsUpdated = @@ROWCOUNT;
    IF @RowsUpdated = 0 RETURN 1;  -- no such order
    RETURN 0;
GO

DECLARE @rows int;
EXEC dbo.usp_SetOrderStatus 1005, N'Shipped', @rows OUTPUT;
SELECT @rows AS RowsUpdated, Status
FROM dbo.Orders WHERE OrderID = 1005;

Output:

RowsUpdated Status    
----------- ----------
          1 Shipped   

(1 rows affected)

How does this work? @@ROWCOUNT holds the count of the statement just before it, so I read it on the very next line. The procedure also returns 1 when no row matched, which the RETURN section uses.

Update multiple columns with multiple parameters

Same orders again, now changing two columns in one call:

CREATE OR ALTER PROCEDURE dbo.usp_UpdateOrder
    @OrderID int,
    @Status  nvarchar(10),
    @Total   decimal(10,2)
AS
    UPDATE dbo.Orders
    SET Status = @Status, Total = @Total
    WHERE OrderID = @OrderID;
GO

EXEC dbo.usp_UpdateOrder @OrderID = 1008, @Status = N'Shipped', @Total = 299.99;
SELECT OrderID, Status, Total FROM dbo.Orders WHERE OrderID = 1008;

Output:

(1 rows affected)
OrderID     Status     Total       
----------- ---------- ------------
       1008 Shipped          299.99

(1 rows affected)

Order 1008 is now Shipped at $299.99, and without SET NOCOUNT ON sqlcmd reports “(1 rows affected)”. For insert-or-update logic, see the if exists update else insert procedure.

Update rows without parameters

This fixed maintenance rule flags orders still pending before 10/10/2026:

CREATE OR ALTER PROCEDURE dbo.usp_FlagStaleOrders
AS
    UPDATE dbo.Orders
    SET Status = N'Review'
    WHERE Status = N'Pending' AND OrderDate < '2026-10-10';
GO

EXEC dbo.usp_FlagStaleOrders;
SELECT OrderID, Status FROM dbo.Orders WHERE Status = N'Review';

Output:

(3 rows affected)
OrderID     Status    
----------- ----------
       1004 Review    
       1005 Review    
       1006 Review    

(3 rows affected)

Orders 1004, 1005 and 1006 move to Review.

How Do You Get the RETURN Value of a Stored Procedure?

Every procedure returns an integer status, 0 unless you RETURN something else. Capture it with EXEC @variable = procedure:

SET NOCOUNT ON;
DECLARE @rc int;

EXEC @rc = dbo.usp_SetOrderStatus @OrderID = 1006, @Status = N'Shipped';
SELECT @rc AS ReturnCode;   -- 0 = updated

EXEC @rc = dbo.usp_SetOrderStatus @OrderID = 9999, @Status = N'Shipped';
SELECT @rc AS ReturnCode;   -- 1 = no such order

Output:

ReturnCode 
-----------
          0
ReturnCode 
-----------
          1

How does this work? Order 1006 exists, so you get 0; order 9999 doesn’t, so you get 1. Declare the int variable first.

Use RETURN for status only. The stored procedure return value guide compares it with OUTPUT, and TRY…CATCH in a stored procedure pairs well with it.

Method 7: Pass a Table-Valued Parameter to a Stored Procedure

To pass a list, like order IDs from an app, create a table type, declare the parameter READONLY, and fill a variable of that type:

IF TYPE_ID(N'dbo.OrderIdList') IS NULL
CREATE TYPE dbo.OrderIdList AS TABLE (OrderID int PRIMARY KEY);
GO
CREATE OR ALTER PROCEDURE dbo.usp_GetOrdersByIds
    @Ids dbo.OrderIdList READONLY
AS
    SELECT o.OrderID, o.Customer, o.Total
    FROM dbo.Orders AS o
    JOIN @Ids AS i ON i.OrderID = o.OrderID;
GO

SET NOCOUNT ON;
DECLARE @ids dbo.OrderIdList;
INSERT INTO @ids VALUES (1001), (1003), (1007);
EXEC dbo.usp_GetOrdersByIds @Ids = @ids;

Output:

OrderID     Customer             Total       
----------- -------------------- ------------
       1001 Megan Bowen                245.50
       1003 Adele Vance               1210.00
       1007 Lynne Robbins              990.40

How does this work? The join returns orders 1001, 1003 and 1007. READONLY is required, so the procedure can read the rows but never change them, and you skip string splitting.

Method 8: Capture the Result Set With INSERT INTO … EXEC

You can’t SELECT * FROM a procedure. To reuse its rows, insert them into a table with matching columns:

SET NOCOUNT ON;
CREATE TABLE #TxOrders (OrderID int, Customer nvarchar(20), Total decimal(10,2));

INSERT INTO #TxOrders
EXEC dbo.usp_OrdersByState @State = 'TX', @MinTotal = 0;

SELECT COUNT(*) AS Orders, SUM(Total) AS Revenue FROM #TxOrders;

Output:

Orders      Revenue                                 
----------- ----------------------------------------
          3                                   680.74

How does this work? The same three Texas orders land in #TxOrders ($680.74). Your table’s columns must match the result set, in order.

See how to select from a stored procedure for more options. Pro Tip: you can’t nest INSERT-EXEC, so use a table-valued function there.

Execute a Stored Procedure in Another Schema or Database

From another database, use the three-part name database.schema.procedure. This runs while connected to master:

-- Connected to master, not DfqExecProc
EXEC DfqExecProc.dbo.usp_OrdersByState @State = 'WA', @MinTotal = 500;
EXEC DfqExecProc.reporting.usp_StateSummary;

Output:

OrderID     Customer             Total       
----------- -------------------- ------------
       1007 Lynne Robbins              990.40
State Orders      Revenue                                 
----- ----------- ----------------------------------------
CA              1                                  1210.00
IL              1                                   312.15
NY              1                                    67.20
TX              3                                   680.74
WA              2                                  1235.90

(5 rows affected)

Lynne Robbins’ $990.40 order is Washington’s only one over $500, and the reporting procedure totals each state. You need EXECUTE permission there.

Rename Result Columns With EXEC … WITH RESULT SETS

WITH RESULT SETS (SQL Server 2012+) renames or retypes the returned columns without touching the procedure:

EXEC dbo.usp_OrdersByState @State = 'WA', @MinTotal = 0
WITH RESULT SETS
(
    (OrderNo int, CustomerName nvarchar(20), OrderTotal decimal(10,2))
);

Output:

OrderNo     CustomerName         OrderTotal  
----------- -------------------- ------------
       1001 Megan Bowen                245.50
       1007 Lynne Robbins              990.40

If the column count doesn’t match, the batch is aborted, and the option isn’t allowed inside INSERT-EXEC.

How Do You Fix “Could not find stored procedure”?

Error 2812 means SQL Server found nothing by that name. Here are the two causes I see most, on the same demo database, each wrapped in TRY…CATCH so the error number prints:

-- Cause 1: the procedure lives in the reporting schema, not dbo
BEGIN TRY EXEC usp_StateSummary; END TRY
BEGIN CATCH PRINT CONCAT(ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;

-- Cause 2: EXEC @sql reads the text as a name
DECLARE @sql nvarchar(200) = N'EXEC dbo.usp_GetOrders';
BEGIN TRY EXEC @sql; END TRY
BEGIN CATCH PRINT CONCAT(ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;

Output:

2812: Could not find stored procedure 'usp_StateSummary'.
2812: Could not find stored procedure 'EXEC dbo.usp_GetOrders'.

The first call has no schema, so SQL Server checks sys and dbo, never reporting. The second hands a whole statement to EXEC @sql, which reads it as a procedure name. Use sp_executesql with real parameters instead:

EXEC sys.sp_executesql
    N'EXEC dbo.usp_OrdersByState @State = @s, @MinTotal = @m;',
    N'@s char(2), @m decimal(10,2)',
    @s = 'CA', @m = 1000;

Output:

OrderID     Customer             Total       
----------- -------------------- ------------
       1003 Adele Vance               1210.00

A wrong database, a typo or a failed CREATE cause it too; our Could not find stored procedure checklist covers each.

How to Run a Stored Procedure in SSMS (Execute Stored Procedure Dialog)

1. In Object Explorer, expand the instance, then Databases.
2. Expand DfqExecProc > Programmability > Stored Procedures.
3. Right-click dbo.usp_OrdersByState and select Execute Stored Procedure.
4. In the Execute Procedure dialog, type each Value (TX, 100) or tick Pass Null Value.
5. Select OK; the rows appear in the Results pane.

Then test the stored procedure with your edge-case values.

Schedule a Stored Procedure With SQL Server Agent

For a nightly run, you put the EXEC line in a T-SQL job step and attach a schedule; here’s how to schedule a stored procedure. Agent isn’t in Express edition, and if jobs never fire, see why SQL Server Agent will not start.

Which Method Should You Use to Execute a Stored Procedure?

Situation	Use
Quick test, no input	EXEC dbo.proc;
Code that lives on	Named parameters
One or two values back	OUTPUT parameters
Success or failure flag	EXEC @rc = ...
A list of IDs in	Table-valued parameter
Rows to reuse in SQL	INSERT INTO ... EXEC
SQL built at run time	sp_executesql

Things to Keep in Mind

• Schema-qualify every call; it prevents most error 2812s.
• Write OUTPUT in the call too, or the variable keeps its old value.
• Pass dates as 'YYYY-MM-DD' or a variable, never a function call.
• Read @@ROWCOUNT right after the UPDATE.
• Nesting stops at 32 levels; check stored procedure execution history to see what ran.

If you work with procedures every day, these are worth a look next:

• Stored procedure vs function in SQL Server
• Execute a SQL function with parameters
• Temp tables inside a stored procedure
• Stored procedure for INSERT in SQL Server

Frequently Asked Questions