Stored Procedures: Parameters, Error Handling, Ownership Chaining and Safe Dynamic SQL

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0.5035.5, Cumulative Update 10) at compatibility level 170; the .NET example was built and run with .NET 10, Microsoft.Data.SqlClient 7.1.1 and Dapper 2.1.89.

Guess First: Is the Order Created When Stock Runs Short?

Almost everyone writes the Bookstore's first order-placing stored procedure roughly like this: open a transaction, insert the order, insert the line, reduce the stock, commit. We use a sequence for the order number:

CREATE SEQUENCE dbo.OrderNumbers AS int START WITH 1;
GO
CREATE OR ALTER PROCEDURE dbo.PlaceOrderNaive
    @CustomerId int, @BookId int, @Quantity smallint
AS
BEGIN TRANSACTION;
INSERT dbo.Orders (OrderNumber, CustomerId, Status, CreatedAt, TotalAmount, Currency)
VALUES (CONCAT('BK-N', NEXT VALUE FOR dbo.OrderNumbers), @CustomerId, 'Pending', SYSUTCDATETIME(), 0, 'TRY');
DECLARE @OrderId int = SCOPE_IDENTITY();
INSERT dbo.OrderItems (OrderId, BookId, Quantity, UnitPrice)
SELECT @OrderId, BookId, @Quantity, Price FROM dbo.Books WHERE BookId = @BookId;
UPDATE dbo.StockItems SET QuantityOnHand -= @Quantity WHERE BookId = @BookId;
COMMIT;

Book 2 has 140 in stock. The customer wants 500. StockItems has a CHECK constraint that keeps the stock from going negative. We'll certainly get an error when we run the procedure; the question is: after the error, does this order exist in the database?

EXEC dbo.PlaceOrderNaive @CustomerId = 42, @BookId = 2, @Quantity = 500;

(1 rows affected)

(1 rows affected)
Msg 547, Level 16, State 1, Server b8be943222fa, Procedure dbo.PlaceOrderNaive, Line 10
The UPDATE statement conflicted with the CHECK constraint "CK_StockItems_Quantity". The conflict occurred in database "sqls_14", table "dbo.StockItems".
The statement has been terminated.

OrderId|OrderNumber|items
-------|-----------|-----
1000001|BK-N1|1

The order and its line were saved, the stock wasn't reduced. There was a transaction and there was an error, but SQL Server didn't roll the transaction back on this error: 547 is a statement-level error, it cancels only that UPDATE, and the procedure continues with the next line, which is COMMIT. The (1 rows affected) messages in the first two lines show another shortcoming of this procedure. In this post we cover the right skeleton for a stored procedure, output parameters and return codes, the permission chain, and writing dynamic SQL safely.

The Right Skeleton: NOCOUNT, XACT_ABORT, TRY/CATCH and THROW

We write the same job in the api schema that the Bookstore exposes to the application, the way we'd put it into production:

CREATE SCHEMA api AUTHORIZATION dbo;
GO
CREATE OR ALTER PROCEDURE api.PlaceOrder
    @CustomerId int,
    @BookId     int,
    @Quantity   smallint,
    @OrderId    int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF @Quantity <= 0
        THROW 50010, N'Quantity must be positive.', 1;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE dbo.StockItems
        SET QuantityOnHand -= @Quantity, UpdatedAt = SYSUTCDATETIME()
        WHERE BookId = @BookId AND QuantityOnHand >= @Quantity;

        IF @@ROWCOUNT = 0
        BEGIN
            ROLLBACK;
            RETURN 1;
        END;

        INSERT dbo.Orders (OrderNumber, CustomerId, Status, CreatedAt, TotalAmount, Currency)
        VALUES (CONCAT('BK-P', NEXT VALUE FOR dbo.OrderNumbers), @CustomerId, 'Pending', SYSUTCDATETIME(), 0,
                CASE WHEN (SELECT Country FROM dbo.Customers WHERE CustomerId = @CustomerId) = 'TR'
                     THEN 'TRY' ELSE 'EUR' END);
        SET @OrderId = SCOPE_IDENTITY();

        INSERT dbo.OrderItems (OrderId, BookId, Quantity, UnitPrice)
        SELECT @OrderId, BookId, @Quantity, Price FROM dbo.Books WHERE BookId = @BookId;

        UPDATE dbo.Orders
        SET TotalAmount = (SELECT SUM(Quantity * UnitPrice) FROM dbo.OrderItems WHERE OrderId = @OrderId)
        WHERE OrderId = @OrderId;

        COMMIT;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;
    END CATCH;
END;

The reasons, line by line:

• SET NOCOUNT ON: turns off the "rows affected" message of each statement (DONE_IN_PROC in the protocol). These messages travel to the client as separate packets; some old clients mistake them for result sets.
• SET XACT_ABORT ON: rolls back the transaction and stops the batch on most run-time errors. The opening 547 can no longer leave the order half done. It also keeps the transaction from staying open when the client cuts the work off, as with a command timeout.
• The stock check comes first and in a single statement: WHERE QuantityOnHand >= @Quantity. Checking with a SELECT first and reducing afterwards opens the door to the race from post 11.
• In CATCH, ROLLBACK first, then a parameterless THROW: it passes the original error to the caller with its number, message and line.

Let's run three scenarios: not enough stock, enough stock, no such customer:

DECLARE @OrderId int, @rc int;

EXEC @rc = api.PlaceOrder @CustomerId = 42, @BookId = 2, @Quantity = 500, @OrderId = @OrderId OUTPUT;
SELECT @rc AS return_code, @OrderId AS order_id, @@TRANCOUNT AS trancount;

EXEC @rc = api.PlaceOrder @CustomerId = 42, @BookId = 2, @Quantity = 1, @OrderId = @OrderId OUTPUT;
SELECT @rc AS return_code, @OrderId AS order_id, TotalAmount, Currency
FROM dbo.Orders WHERE OrderId = @OrderId;
GO
DECLARE @OrderId int, @rc int;
EXEC @rc = api.PlaceOrder @CustomerId = 999999, @BookId = 2, @Quantity = 1, @OrderId = @OrderId OUTPUT;
GO
SELECT @@TRANCOUNT AS trancount, (SELECT QuantityOnHand FROM dbo.StockItems WHERE BookId = 2) AS stock_book_2;

return_code|order_id|trancount
-----------|--------|---------
1|NULL|0

return_code|order_id|TotalAmount|Currency
-----------|--------|-----------|--------
0|1000002|39.00|TRY

Msg 547, Level 16, State 1, Server b8be943222fa, Procedure api.PlaceOrder, Line 27
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Orders_Customers". The conflict occurred in database "sqls_14", table "dbo.Customers", column 'CustomerId'.

trancount|stock_book_2
---------|------------
0|139

When the stock wasn't enough, it returned return code 1, not an error; that's an expected business outcome and shouldn't be an exception. When the customer didn't exist, the foreign key error went to the caller as it was, but the stock reduction was rolled back: only the successful order took it from 140 to 139. Use the return code only to report status (0 for success, positive numbers for known business outcomes); returning data is the job of output parameters or result sets.

THROW or RAISERROR?

In old code you'll see RAISERROR everywhere. The difference between the two is what happens after the error:

BEGIN TRANSACTION;
RAISERROR(N'Payment failed for order %d.', 16, 1, 1000002);
SELECT 'after RAISERROR' AS step, @@TRANCOUNT AS trancount;
THROW 50020, N'Payment failed for order 1000002.', 1;
SELECT 'after THROW' AS step;
GO
SELECT 'next batch' AS step, @@TRANCOUNT AS trancount;
ROLLBACK;

Msg 50000, Level 16, State 1, Server b8be943222fa, Line 2
Payment failed for order 1000002.
step|trancount
----|---------
after RAISERROR|1

Msg 50020, Level 16, State 1, Server b8be943222fa, Line 4
Payment failed for order 1000002.
step|trancount
----|---------
next batch|1

RAISERROR wrote the error and the code carried on; a COMMIT after it would have saved the half-done work. THROW stopped the batch: after THROW never ran. Neither rolled back the transaction by itself; in the next batch @@TRANCOUNT is still 1. That's why XACT_ABORT ON and the ROLLBACK in CATCH are each needed. Use THROW in new code; keep RAISERROR only for special cases such as printing progress messages WITH NOWAIT. We saw the engine side of transactions in the 9th post of the series (Transactions and the Transaction Log).

Output Parameters, Return Codes and Dapper

On the application side we call the same procedure with Dapper. We covered output parameters and return values in detail in the stored procedure post of the Dapper series; here is just the skeleton:

public sealed record PlaceOrderResult(int ReturnCode, int? OrderId);

public sealed class OrderGateway(SqlConnection connection)
{
    public async Task<PlaceOrderResult> PlaceOrderAsync(int customerId, int bookId, short quantity,
        CancellationToken ct = default)
    {
        DynamicParameters parameters = new();
        parameters.Add("CustomerId", customerId, DbType.Int32);
        parameters.Add("BookId", bookId, DbType.Int32);
        parameters.Add("Quantity", quantity, DbType.Int16);
        parameters.Add("OrderId", dbType: DbType.Int32, direction: ParameterDirection.Output);
        parameters.Add("ReturnCode", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

        await connection.ExecuteAsync(new CommandDefinition("api.PlaceOrder", parameters,
            commandType: CommandType.StoredProcedure, cancellationToken: ct));

        return new PlaceOrderResult(parameters.Get<int>("ReturnCode"), parameters.Get<int?>("OrderId"));
    }
}

PlaceOrderResult { ReturnCode = 0, OrderId = 1000005 }
PlaceOrderResult { ReturnCode = 1, OrderId =  }
SqlException 50010: Quantity must be positive. (procedure api.PlaceOrder, line 12)

commandType: CommandType.StoredProcedure matters: SqlClient sends the request as an RPC (Remote Procedure Call), the parameters travel typed, and the procedure's own plan in the plan cache is used. Your own error numbers (50010) arrive in SqlException.Number; the application can map them to 400 Bad Request and unknown ones to 500.

Permissions: Ownership Chaining and EXECUTE Only

The least discussed benefit of stored procedures is security. You can give the application user no permissions on the tables at all, only the right to execute procedures. For testing we create a database user that doesn't need a login:

CREATE ROLE app_runtime;
GRANT EXECUTE ON SCHEMA::api TO app_runtime;
CREATE USER sqls_14_app WITHOUT LOGIN;
ALTER ROLE app_runtime ADD MEMBER sqls_14_app;
GO
EXECUTE AS USER = 'sqls_14_app';
SELECT TOP (1) OrderId FROM dbo.Orders;
GO
DECLARE @OrderId int, @rc int;
EXEC @rc = api.PlaceOrder @CustomerId = 42, @BookId = 3, @Quantity = 1, @OrderId = @OrderId OUTPUT;
SELECT USER_NAME() AS current_user_name, @rc AS return_code, @OrderId AS order_id;
REVERT;

Msg 229, Level 14, State 5, Server b8be943222fa, Line 2
The SELECT permission was denied on the object 'Orders', database 'sqls_14', schema 'dbo'.
current_user_name|return_code|order_id
-----------------|-----------|--------
sqls_14_app|0|1000004

The user can't read Orders but can run the procedure that writes to the same table. Ownership chaining makes that possible: if the owner of api.PlaceOrder (the schema owner, dbo) and the owner of the tables it accesses are the same, SQL Server doesn't check permissions further down the chain. It means that on the day the application user is compromised, the attacker can't run SELECT * FROM dbo.Customers. One detail: the order number is 1000004, because the INSERT rolled back when the customer wasn't found used up 1000003. Identity values don't come back on rollback.

Safe Dynamic SQL

The order search screen works with a customer, an optional status, and a sort column chosen by the user. The sort column can't be a parameter, because SQL identifiers can't be passed as parameters. So we write dynamic SQL, but with rules: values as sp_executesql parameters, identifiers through an allowlist and QUOTENAME:

CREATE OR ALTER PROCEDURE api.SearchOrders
    @CustomerId     int,
    @Status         varchar(20) = NULL,
    @SortColumn     sysname = N'CreatedAt',
    @SortDescending bit = 1,
    @Top            int = 3,
    @Debug          bit = 0
AS
BEGIN
    SET NOCOUNT ON;

    IF @SortColumn NOT IN (N'CreatedAt', N'TotalAmount', N'OrderNumber')
        THROW 50030, N'Invalid sort column.', 1;

    DECLARE @sql nvarchar(max) = N'SELECT TOP (@Top) OrderId, OrderNumber, Status, CreatedAt, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId'
        + CASE WHEN @Status IS NOT NULL THEN N' AND Status = @Status' ELSE N'' END
        + N'
ORDER BY ' + QUOTENAME(@SortColumn) + CASE WHEN @SortDescending = 1 THEN N' DESC;' ELSE N' ASC;' END;

    IF @Debug = 1 PRINT @sql;

    EXEC sys.sp_executesql @sql,
        N'@CustomerId int, @Status varchar(20), @Top int',
        @CustomerId = @CustomerId, @Status = @Status, @Top = @Top;
END;

EXEC api.SearchOrders @CustomerId = 42, @SortColumn = N'TotalAmount', @Debug = 1;
EXEC api.SearchOrders @CustomerId = 42, @SortColumn = N'CreatedAt; DROP TABLE dbo.Orders; --';

SELECT TOP (@Top) OrderId, OrderNumber, Status, CreatedAt, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
ORDER BY [TotalAmount] DESC;
OrderId|OrderNumber|Status|CreatedAt|TotalAmount
-------|-----------|------|---------|-----------
540544|BK-00540544|Delivered|2025-01-20 18:06:00|269.90
783424|BK-00783424|Delivered|2025-12-24 02:06:00|240.79
67681|BK-00067681|Delivered|2023-04-05 00:00:00|218.87
Msg 50030, Level 16, State 1, Server b8be943222fa, Procedure api.SearchOrders, Line 13
Invalid sort column.

No value from the user appears as text in the generated SQL; only parameter names do. When @Status isn't given, the condition isn't added to the SQL at all, so two different query shapes get two separate, correct plans. The allowlist rejected the injection attempt before it reached SQL. QUOTENAME is the second line of defense: even if the allowlist is loosened one day, it wraps the column name in square brackets and escapes any ] inside. We covered SQL injection itself in the OWASP (Open Worldwide Application Security Project) A05 Injection post of the Web Security series; the topic here is how to write dynamic SQL inside the database.

Dynamic SQL breaks the chain: module signing

Running the same procedure as the application user gives:

Msg 229, Level 14, State 5, Server b8be943222fa, Line 1
The SELECT permission was denied on the object 'Orders', database 'sqls_14', schema 'dbo'.

The text run by sp_executesql is a separate batch and isn't part of the ownership chain; permissions are checked against the calling user. Giving the user SELECT would throw away the whole gain. There are two fixes. WITH EXECUTE AS OWNER runs the procedure with its owner's identity; simple, but everything inside the procedure runs with dbo permissions and functions like USER_NAME() no longer show the real user. The narrower one is module signing: you sign the procedure with a certificate and give the needed permission only to the user created from that certificate:

CREATE CERTIFICATE SearchOrdersSigner
    ENCRYPTION BY PASSWORD = '$(CertPassword)'
    WITH SUBJECT = 'Signs api.SearchOrders';
ADD SIGNATURE TO api.SearchOrders
    BY CERTIFICATE SearchOrdersSigner WITH PASSWORD = '$(CertPassword)';
ALTER CERTIFICATE SearchOrdersSigner REMOVE PRIVATE KEY;

CREATE USER SearchOrdersSigner FROM CERTIFICATE SearchOrdersSigner;
GRANT SELECT ON dbo.Orders TO SearchOrdersSigner;
GO
EXECUTE AS USER = 'sqls_14_app';
EXEC api.SearchOrders @CustomerId = 42;
SELECT TOP (1) OrderId FROM dbo.Orders;
REVERT;

OrderId|OrderNumber|Status|CreatedAt|TotalAmount
-------|-----------|------|---------|-----------
1000004|BK-P4|Pending|2026-11-08 12:06:07|35.69
1000002|BK-P2|Pending|2026-11-08 12:05:57|39.00
1000001|BK-N1|Pending|2026-11-08 12:05:21|.00
Msg 229, Level 14, State 5, Server b8be943222fa, Line 3
The SELECT permission was denied on the object 'Orders', database 'sqls_14', schema 'dbo'.

The procedure works, but the user still can't reach the table directly. $(CertPassword) is a sqlcmd variable; we pass the password at run time with -v CertPassword=... instead of writing it into the script. Removing the private key keeps the certificate from being used to sign any other module. The price: the moment you ALTER the procedure, the signature is dropped. We tried it, and the signature count in sys.crypt_properties went from 1 to 0. Your deploy script must sign again with a new certificate after every procedure change.

Procedures or ORM? And Versioning

The "should business logic live in stored procedures or in the application" debate has two reasonable answers. Staying on the ORM (Object-Relational Mapping) side, that is Entity Framework Core (EF Core): business logic in one language, easy unit testing, refactoring tools work, switching databases is possible. Staying on the procedure side: data is processed where it lives and round trips drop; permissions can be granted at procedure level; a DBA (Database Administrator) can change a plan without deploying the application. A good middle ground for an application like the Bookstore: CRUD (Create, Read, Update, Delete) and simple queries in EF Core, and a few operations that touch many tables and where consistency is critical (placing an order, reserving stock), plus dynamic reporting queries, in procedures.

Whichever you choose, procedures must live in the repo. There are two common routes. SQL Database Projects, that is the Microsoft.Build.Sql SDK (Software Development Kit), keeps the schema and procedures as .sql files, validates references at build time and produces a DACPAC (Data-tier Application Package); the deploy tool computes the difference against the target database. If you use EF Core migrations, you put the procedure inside a migration with migrationBuilder.Sql(...); simple, but every change is a new migration and you have to search the migrations to see the procedure's latest version. Either way, CREATE OR ALTER makes the scripts idempotent.

What's Different in SQL Server 2019/2022

Everything in this post works the same on SQL Server 2019 and 2022: CREATE OR ALTER has existed since 2016 SP1 (Service Pack 1), THROW since 2012; ownership chaining and module signing haven't changed. The difference is in the plans of procedures with optional parameters. "Catch-all" procedures written with WHERE (@Status IS NULL OR Status = @Status) instead of dynamic SQL get a single, often bad plan on 2019; the fix is OPTION (RECOMPILE) or dynamic SQL as in this post. SQL Server 2022, at compatibility level 160, can keep several plans for equality parameters with skewed distributions through Parameter Sensitive Plan optimization; 2025, at compatibility level 170, adds optional parameter plan optimization for such optional-parameter patterns. We measured these in the 7th post of the series (Plan Cache, Parameterization and Parameter Sniffing). If you also target older versions, dynamic SQL is still the most predictable route.

Watch Out in Production

Manage error numbers like a contract. Keep the numbers above 50000 (50010, 50030) in a document and bind them to an enum on the application side; code that decides by message text breaks when the message changes.

Monitor procedure performance per statement. sys.dm_exec_procedure_stats gives, for each procedure, the execution count, total logical reads and CPU (Central Processing Unit); Query Store shows each statement inside the procedure separately. You need to find not that a procedure got slower, but which statement inside it did.

Separate the application user. The app_runtime role in this post gets only EXECUTE; the user that runs migrations is separate and has DDL (Data Definition Language) permissions. We'll build the security model as a whole in post 17.

Make transaction ownership inside procedures explicit. If the caller already opened a transaction, the ROLLBACK in the procedure rolls back the caller's transaction too and raises the @@TRANCOUNT mismatch error (266). Either set the rule "the procedure manages its own transaction and isn't called from inside one" or choose a pattern that checks @@TRANCOUNT and uses savepoints.

Common Pitfalls

Building dynamic SQL inside a procedure by concatenating strings. EXEC('... WHERE Status = ''' + @Status + '''') is believing you're safe because you use a procedure. Injection doesn't care where the SQL is concatenated.

Naming procedures with the sp_ prefix. SQL Server looks up names starting with sp_ in master first; both a small cost and a risk of colliding with a system procedure.

Swallowing the error in CATCH. BEGIN CATCH SELECT ERROR_MESSAGE(); END CATCH turns the error into a result set; the application sees a successful call. The error must either go up with THROW or become a deliberate return code.

Forgetting to read the output parameter, or reading it too early. In ActiveX Data Objects for .NET (ADO.NET), output parameters and the return value are filled after the result sets have been read completely and the reader is closed. If a procedure returns both a result set and an output, a value read before closing the reader comes back null.

A Small Experiment

★☆☆ Add only SET XACT_ABORT ON to the top of dbo.PlaceOrderNaive and repeat the opening call. Is the order created? What's @@TRANCOUNT?

★★☆ Add optional @CreatedFrom and @CreatedTo parameters to api.SearchOrders. Look at the four different query shapes produced with @Debug = 1 and count how many separate plans appear in the plan cache with sys.dm_exec_query_stats.

★★★ Rewrite api.SearchOrders with WITH EXECUTE AS OWNER instead of signing it. Add SELECT USER_NAME(), ORIGINAL_LOGIN() inside the procedure and compare what the two methods return. Which one is better for auditing?

Procedures are the most visible form of code running inside the database. There are less visible ones too: functions called for every row and triggers that run silently after every DML (Data Manipulation Language) statement. In the next post we measure scalar UDF (User-Defined Function) inlining, inline and multi-statement table-valued functions, and trigger pitfalls.