SpDev Install free

How to execute a stored procedure in SQL Server: every way, with examples

EXEC with named and positional parameters, defaults, OUTPUT parameters, return codes, INSERT...EXEC, WITH RESULT SETS, calling one procedure from another, and the errors you'll meet.

By Areeb Ahmer, . 5 minute read.

Running a stored procedure is one line: EXEC dbo.ProcedureName. Passing parameters, getting values back and capturing what it returns have a few more rules, and a few error messages you’ll meet sooner or later. This article covers them all, with examples you can run against the SpDevDemo database.

Two procedures to call:

CREATE OR ALTER PROCEDURE dbo.GetOrders
    @CustomerId INT,
    @Status     VARCHAR(20) = NULL,       -- optional: NULL means any status
    @Top        INT = 5
AS
BEGIN
    SET NOCOUNT ON;
    SELECT TOP (@Top) OrderId, OrderNumber, Status, Total
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
      AND (@Status IS NULL OR Status = @Status)
    ORDER BY OrderDate DESC;
END
GO

CREATE OR ALTER PROCEDURE dbo.CountOrders
    @CustomerId INT,
    @Count      INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @Count = COUNT(*) FROM dbo.Orders WHERE CustomerId = @CustomerId;
    RETURN CASE WHEN @Count = 0 THEN 1 ELSE 0 END;
END

EXEC with named parameters

EXEC dbo.GetOrders @CustomerId = 42, @Status = 'Open';

Name the parameters. The call still works when someone adds a parameter or changes their order, and it says what each value is. EXEC and EXECUTE are the same keyword.

Positional parameters

EXEC dbo.GetOrders 42, 'Open', 3;

Values are matched to parameters in declaration order. You can start positionally and switch to names, but not the other way round:

EXEC dbo.GetOrders @CustomerId = 42, 'Open';
Msg 119, Level 15, State 1
Must pass parameter number 2 and subsequent parameters as '@name = value'.
After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Default values

A parameter with a default in the declaration (@Top INT = 5) can be left out. To skip one in the middle of a positional call, write DEFAULT:

EXEC dbo.GetOrders 42, DEFAULT, 2;    -- any status, top 2

A parameter without a default must be passed. Leave out @CustomerId and you get Procedure or function ‘GetOrders’ expects parameter ‘@CustomerId’, which was not supplied.

OUTPUT parameters and the return code

DECLARE @n INT, @rc INT;

EXEC @rc = dbo.CountOrders @CustomerId = 42, @Count = @n OUTPUT;

SELECT @rc AS ReturnCode, @n AS Orders;
ReturnCode  Orders
----------  ------
0           9

Two rules people trip over:

  • The caller must write OUTPUT (or OUT) too. Without it, the call succeeds but your variable isn’t changed.
  • EXEC @rc = ... captures the return code. A procedure without a RETURN statement returns 0.

Capture a result set: INSERT…EXEC

To work with the rows a procedure returns, insert them into a table whose columns match:

CREATE TABLE #orders (OrderId INT, OrderNumber VARCHAR(20), Status VARCHAR(20), Total DECIMAL(12, 2));

INSERT INTO #orders (OrderId, OrderNumber, Status, Total)
EXEC dbo.GetOrders @CustomerId = 1, @Top = 100;

SELECT COUNT(*) AS Captured, SUM(Total) AS Total FROM #orders;

INSERT...EXEC has limits worth knowing before you build on it:

  • The procedure must return exactly one shape of result. Several result sets with the same columns are all inserted; different shapes fail.
  • It can’t be nested. If the procedure you call uses INSERT...EXEC itself, you get Msg 8164: An INSERT EXEC statement cannot be nested.
  • The insert and the procedure run in one transaction. A ROLLBACK inside the procedure fails the whole thing.

When you control the procedure, an output parameter, a temp table the procedure fills, or an inline table-valued function is usually a better way to pass rows around.

Rename or check the result: WITH RESULT SETS

WITH RESULT SETS (SQL Server 2012 and later) declares the shape you expect. It renames and converts the columns, and fails clearly if the procedure returns something else:

EXEC dbo.GetOrders @CustomerId = 42, @Top = 2
WITH RESULT SETS ((
    Id      INT,
    Number  VARCHAR(20),
    State   VARCHAR(20),
    Amount  DECIMAL(12, 2)
));

It’s also how you give a stable contract to callers that read columns by name, while the procedure’s internals change.

Execute a procedure from another procedure

Inside a procedure, call another one the same way:

CREATE OR ALTER PROCEDURE dbo.CustomerReport @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @n INT;

    EXEC dbo.CountOrders @CustomerId = @CustomerId, @Count = @n OUTPUT;
    IF @n = 0 RETURN;

    EXEC dbo.GetOrders @CustomerId = @CustomerId, @Top = 3;
END
GO

EXEC dbo.CustomerReport @CustomerId = 42;

The result sets of the inner procedure go straight to the client, as part of the outer call. Procedures can nest 32 levels deep. Temp tables created by the caller are visible to the procedures it calls, which is a common way to pass a set of rows down. (A temp table created inside the called procedure disappears when it returns.)

Procedures in another database or server

EXEC OtherDb.dbo.GetOrders @CustomerId = 42;               -- another database, same server
EXEC [LINKEDSRV].SalesDb.dbo.GetOrders @CustomerId = 42;   -- through a linked server

For a linked server, the server must allow RPC (EXEC sp_serveroption 'LINKEDSRV', 'rpc out', 'true').

A procedure whose name is in a variable

EXEC accepts a variable holding the name:

DECLARE @proc SYSNAME = N'dbo.GetOrders';
EXEC @proc @CustomerId = 42, @Top = 1;

Only use this with names from a fixed list you control, never with text from a user. For dynamic SQL in general, use sp_executesql with parameters, not string concatenation.

From SSMS and VS Code

  • SSMS: right-click the procedure in Object Explorer, choose Execute Stored Procedure…, fill in the values, and SSMS writes and runs the EXEC for you.
  • VS Code: with the MSSQL extension, run an EXEC statement from any query editor. With SpDev, open the procedure’s .sql file and click Run above it. SpDev asks for each parameter, remembers your values for next time, and runs your local file, so you can test a change before deploying it. Debug does the same and stops on the first line.

From .NET

using var command = new SqlCommand("dbo.CountOrders", connection) { CommandType = CommandType.StoredProcedure };
command.Parameters.Add("@CustomerId", SqlDbType.Int).Value = 42;
var count = command.Parameters.Add("@Count", SqlDbType.Int);
count.Direction = ParameterDirection.Output;
await command.ExecuteNonQueryAsync();

ADO.NET, Dapper and EF Core each have their own way to pass output parameters and read return codes; the C# article covers all three.