Stored Procedures

A stored procedure is a named block of SQL statements, saved in the database, that you run by name and can pass parameters to.

Unlike a view, it can contain several statements, variables, conditions, loops and transactions. That makes it capable of holding real logic — which is both the appeal and the thing to be careful about.

CREATE OR ALTER PROCEDURE usp_get_customer_orders
    @customer_id INT,
    @from_date   DATE,
    @to_date     DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        o.order_id,
        o.order_date,
        o.status,
        SUM(oi.quantity * oi.unit_price) + o.shipping_fee AS order_total
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.customer_id = @customer_id
      AND o.order_date >= @from_date
      AND o.order_date <  @to_date
    GROUP BY o.order_id, o.order_date, o.status, o.shipping_fee
    ORDER BY o.order_date DESC;
END;

-- Running it
EXEC usp_get_customer_orders
    @customer_id = 417,
    @from_date   = '2026-01-01',
    @to_date     = '2027-01-01';

• Parameters are declared with a type, like columns. They are passed as values, which means the database treats them as data and never as part of the SQL text — that property is why parameters are the defence against injection, covered in the security lesson.
• CREATE OR ALTER creates the procedure or replaces it if it exists, which suits a script you run repeatedly. It is supported from SQL Server 2016 SP1; earlier versions need a DROP then CREATE.
• SET NOCOUNT ON suppresses the "n rows affected" messages. On a procedure called frequently from an application, those messages are pure overhead.
• @to_date is used with < rather than <=, so a caller passing the first of the next month gets the whole previous month regardless of any time component. This is the same date-range care from the WHERE lesson.

CREATE OR ALTER PROCEDURE usp_cancel_order
    @order_id    INT,
    @reason      NVARCHAR(200),
    @rows_changed INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE orders
        SET status = 'cancelled'
        WHERE order_id = @order_id
          AND status IN ('pending', 'processing');

        SET @rows_changed = @@ROWCOUNT;

        IF @rows_changed = 0
        BEGIN
            ROLLBACK TRANSACTION;
            THROW 50001, 'Order not found, or not in a cancellable state.', 1;
        END;

        INSERT INTO order_status_log (order_id, new_status, reason, changed_at)
        VALUES (@order_id, 'cancelled', @reason, SYSUTCDATETIME());

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

• Two statements that must both succeed: the status change and the log entry. The transaction makes them one unit, so a failure cannot leave a cancelled order with no audit record.
• @@ROWCOUNT holds the number of rows the previous statement affected. Checking it is how the procedure tells "cancelled" from "there was nothing to cancel", which are different outcomes the caller needs to distinguish.
• The status IN ('pending', 'processing') condition means a shipped order cannot be cancelled by this path. Encoding that rule here is exactly the kind of decision the trade-off section discusses.
• THROW re-raises the original error so the caller sees the real cause rather than a generic failure. XACT_STATE() checks whether a transaction is still open before attempting to roll back.
• An OUTPUT parameter returns a value to the caller alongside any result sets.

Stored procedures are frequently presented as a best practice. They are not automatically good, and the costs are real enough that many teams deliberately keep logic out of the database.

The core issue is that a procedure is a deployed object in a database, not a file in your repository. Everything your development process does for application code — version control, code review, automated tests, refactoring tools — has to be recreated for procedures, and usually ends up weaker.