SQL for .NET developers Intermediate

Stored procedures

A stored procedure is named, reusable T-SQL saved in the database. This lesson shows how to create one with parameters, output parameters and return values, when procedures are a good idea for .NET apps, and how to call one safely from C#.

One call, many steps
  1. StartApp calls dbo.PlaceOrderOne round trip with typed parameters
  2. Step 2Check and update stockFails early with THROW if stock is short
  3. Step 3Insert order and linesInside one transaction
  4. Step 4Return OrderIdThrough an OUTPUT parameter
  5. EndCommit or roll backXACT_ABORT makes it all or nothing

What a procedure gives you

A procedure bundles several statements behind one name with typed parameters. It runs close to the data, so a multi-step job (check stock, insert the order, update stock) needs one round trip instead of several.

Permissions can be granted on the procedure alone, so an app account can run it without direct access to the tables. Plans are cached and reused, like parameterized queries.

Creating one

SQL
CREATE OR ALTER PROCEDURE dbo.PlaceOrder
    @CustomerId int,
    @ProductId  int,
    @Quantity   int,
    @OrderId    int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;       -- skip "n rows affected" messages
    SET XACT_ABORT ON;    -- any error rolls back the transaction

    BEGIN TRAN;
        UPDATE dbo.Products
        SET Stock = Stock - @Quantity
        WHERE ProductId = @ProductId AND Stock >= @Quantity;

        IF @@ROWCOUNT = 0
            THROW 50001, 'Not enough stock.', 1;

        INSERT dbo.Orders (CustomerId) VALUES (@CustomerId);
        SET @OrderId = SCOPE_IDENTITY();

        INSERT dbo.OrderLines (OrderId, ProductId, Quantity)
        VALUES (@OrderId, @ProductId, @Quantity);
    COMMIT;
END;

Calling it from C#

Use CommandType.StoredProcedure and typed parameters. Never build the EXEC string by joining user input.

C#
await using var conn = new SqlConnection(connectionString);
await using var cmd = new SqlCommand("dbo.PlaceOrder", conn)
{
    CommandType = CommandType.StoredProcedure
};
cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = customerId;
cmd.Parameters.Add("@ProductId", SqlDbType.Int).Value = productId;
cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = quantity;
var orderId = cmd.Parameters.Add("@OrderId", SqlDbType.Int);
orderId.Direction = ParameterDirection.Output;

await conn.OpenAsync();
await cmd.ExecuteNonQueryAsync();
int newId = (int)orderId.Value;

When to use them

  • Good fits: multi-step operations that must be atomic, heavy set-based processing, and shared logic used by several apps or reports.
  • Less good: simple CRUD that EF Core already handles well; business rules that change often are easier to test in C#.
  • Keep procedures in source control and deploy them with migrations or a database project so every environment matches.
  • Use CREATE OR ALTER so the script can run again safely.

Interview question

What does SET XACT_ABORT ON do in a stored procedure?

It makes SQL Server roll back the whole transaction and stop the batch when a run-time error occurs. Without it, some errors only fail the current statement and the transaction stays open with partial work, which can leave data inconsistent and hold locks. With it on, a procedure either completes fully or changes nothing.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.