Day 17: Stored procedures

A. Lesson overview

Activity breakdown

Business scenario. The customer-service application team wants a small set of well-defined database “APIs”: look up a customer’s summary, add a customer, transfer stock between warehouses, and search orders with any combination of filters. Each should validate its inputs, behave predictably on errors and be safe to expose to users who should not have direct table access.

B. Theory explained clearly

Part 1 — What a stored procedure is

A stored procedure is a named, parameterised batch of T-SQL stored in the database. It can contain almost any statements: queries, data changes, transactions, control of flow, calls to other procedures.

Functions (Day 18) return values or tables and can be used inside queries; procedures cannot be used inside a SELECT, but can change data, manage transactions and return several result sets.

Part 2 — Creating and executing

CREATE OR ALTER PROCEDURE Lab.usp_GetCustomerOrders
    @CustomerID int,
    @FromDate   date = '2024-01-01',          -- default: optional parameter
    @ToDate     date = '2026-01-01'
AS
BEGIN
    SET NOCOUNT ON;                            -- suppress "(n rows affected)" messages

    SELECT  OrderID, OrderDate, OrderStatus, SalesChannel
    FROM    Sales.SalesOrder
    WHERE   CustomerID = @CustomerID
      AND   OrderDate >= @FromDate
      AND   OrderDate <  @ToDate
    ORDER BY OrderDate, OrderID;
END;
GO

Executing:

EXEC Lab.usp_GetCustomerOrders @CustomerID = 1, @FromDate = '2025-12-01';   -- named: clear, order-independent
EXEC Lab.usp_GetCustomerOrders 1, '2025-12-01', '2026-01-01';               -- positional: fragile
EXEC Lab.usp_GetCustomerOrders @CustomerID = 1, @ToDate = DEFAULT;          -- explicitly use a default

Prefer named parameters: adding a parameter later then can’t silently shift positional arguments. Parameters can’t take expressions directly (@FromDate = DATEADD(…) is not allowed in EXEC); compute into a variable first.

Inspect a procedure’s definition with EXEC sp_helptext 'Lab.usp_GetCustomerOrders'; or SELECT OBJECT_DEFINITION(OBJECT_ID('Lab.usp_GetCustomerOrders'));, or in Object Explorer under Programmability → Stored Procedures.

Part 3 — Control of flow

Prefer set-based statements over WHILE loops for row processing; loops are for batching (Day 15’s stretch), retries and genuinely procedural logic.

Part 4 — Returning information

CREATE OR ALTER PROCEDURE Lab.usp_GetCustomerStats
    @CustomerID  int,
    @Orders      int           OUTPUT,
    @LifetimeNet decimal(12,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (SELECT 1 FROM Sales.Customer WHERE CustomerID = @CustomerID)
        RETURN 1;                                     -- 1 = customer not found
    SELECT @Orders = COUNT(*) …;
    RETURN 0;
END;
GO

DECLARE @o int, @n decimal(12,2), @rc int;
EXEC @rc = Lab.usp_GetCustomerStats @CustomerID = 12, @Orders = @o OUTPUT, @LifetimeNet = @n OUTPUT;
SELECT @rc AS ReturnCode, @o AS Orders, @n AS LifetimeNet;

The keyword OUTPUT is required both in the declaration and in the call; forgetting it in the call is not an error — the variable simply stays unchanged. A procedure that ends without RETURN returns 0. Error handling should use errors (THROW), not only return codes; codes are for expected outcomes (“not found”, “refused”).

INSERT INTO #t EXEC proc … captures a procedure’s result set into a table — the column list must match, the procedure must return a single compatible shape, and INSERT … EXEC cannot be nested.

Part 5 — Procedures that change data

A dependable pattern (completed with TRY…CATCH on Day 20):

CREATE OR ALTER PROCEDURE Lab.usp_DoSomething
    @… 
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;                     -- any error rolls back and stops

    -- 1. Validate inputs (cheap checks first); RETURN a code for expected refusals
    -- 2. BEGIN TRANSACTION
    -- 3. Do the work with set-based statements, locking what you read if you write it back
    -- 4. COMMIT TRANSACTION
    -- 5. Report: OUTPUT parameters / result set; RETURN 0
END;

Settings such as SET NOCOUNT, SET XACT_ABORT and SET TRANSACTION ISOLATION LEVEL made inside a procedure revert when it ends, so they don’t leak into the caller.

Part 6 — Dynamic SQL inside procedures, and security

The “catch-all” search problem

A search screen has five optional filters. A static query covering every combination looks like:

WHERE (@CustomerID IS NULL OR o.CustomerID = @CustomerID)
  AND (@Status     IS NULL OR o.OrderStatus = @Status)
  …

It works, but SQL Server compiles one plan for all combinations, which is often poor (Day 24). Two common remedies:

1. Add OPTION (RECOMPILE) so each execution gets a plan for its actual values.
2. Build the WHERE clause dynamically with only the supplied filters, and run it with sp_executesql, passing every value as a parameter:

DECLARE @sql nvarchar(max) = N'SELECT … FROM Sales.SalesOrder AS o WHERE 1 = 1';
IF @CustomerID IS NOT NULL SET @sql += N' AND o.CustomerID = @CustomerID';
IF @Status     IS NOT NULL SET @sql += N' AND o.OrderStatus = @Status';
SET @sql += N' ORDER BY o.OrderDate, o.OrderID;';

EXEC sys.sp_executesql @sql,
     N'@CustomerID int, @Status varchar(10)',          -- parameter definitions
     @CustomerID = @CustomerID, @Status = @Status;      -- values

The text contains @CustomerID, not the customer’s number. Each distinct text gets its own cached plan, reused across calls with different values. Never write … + CAST(@CustomerID AS nvarchar(10)) or … + @Status — concatenating values is how SQL injection happens (Day 28).

Permissions and ownership chaining

GRANT EXECUTE ON Lab.usp_GetCustomerOrders TO SomeRole;

When a procedure and the tables it uses have the same owner (here dbo owns every schema), SQL Server does not check the caller’s permissions on those tables — ownership chaining. The caller needs only EXECUTE. Dynamic SQL breaks the chain: statements run by sp_executesql are checked against the caller’s own permissions, unless the procedure is signed with a certificate or uses EXECUTE AS. Keep that in mind when you move a static procedure to dynamic SQL.

C. Dataset and starting state

Setup (re-runnable; resets only the two Lab copies):

USE RetailLab;
GO

DROP TABLE IF EXISTS Lab.Customer, Lab.Stock;

SELECT * INTO Lab.Customer FROM Sales.Customer;
ALTER TABLE Lab.Customer ADD CONSTRAINT PK_LabCustomer PRIMARY KEY (CustomerID);
CREATE UNIQUE INDEX UX_LabCustomer_Email ON Lab.Customer (Email) WHERE Email IS NOT NULL;

SELECT WarehouseID, ProductID, QuantityOnHand, ReorderLevel INTO Lab.Stock FROM Inventory.Stock;
ALTER TABLE Lab.Stock ADD CONSTRAINT PK_LabStock PRIMARY KEY (WarehouseID, ProductID);
ALTER TABLE Lab.Stock ADD CONSTRAINT CK_LabStock_NonNegative CHECK (QuantityOnHand >= 0);
GO

Validation. Expected: 40, 1142, 0.

SELECT  (SELECT COUNT(*) FROM Lab.Customer)                  AS LabCustomers,
        (SELECT SUM(QuantityOnHand) FROM Lab.Stock)          AS TotalUnits,
        (SELECT COUNT(*) FROM sys.procedures WHERE schema_id = SCHEMA_ID('Lab')) AS LabProcedures;

(LabProcedures may be higher if you have done this lesson before; that is fine — CREATE OR ALTER replaces them.)

D. Worked examples

Worked example 1 — A simple procedure with defaults

CREATE OR ALTER PROCEDURE Lab.usp_GetCustomerOrders
    @CustomerID int,
    @FromDate   date = '2024-01-01',
    @ToDate     date = '2026-01-01'
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  OrderID, OrderDate, OrderStatus, SalesChannel
    FROM    Sales.SalesOrder
    WHERE   CustomerID = @CustomerID
      AND   OrderDate >= @FromDate
      AND   OrderDate <  @ToDate
    ORDER BY OrderDate, OrderID;
END;
GO

EXEC Lab.usp_GetCustomerOrders @CustomerID = 1, @FromDate = '2025-12-01';
EXEC Lab.usp_GetCustomerOrders @CustomerID = 1;          -- all dates via defaults

Expected result: first call: 3 rows — 1350 (2025-12-03, Delivered), 1372 (Pending), 1373 (Pending). Second call: all 27 of customer 1’s orders. Calling without @CustomerID fails: Procedure or function ‘usp_GetCustomerOrders’ expects parameter ‘@CustomerID’, which was not supplied.

Worked example 2 — OUTPUT parameters and a return code

CREATE OR ALTER PROCEDURE Lab.usp_GetCustomerStats
    @CustomerID  int,
    @Orders      int           OUTPUT,
    @LifetimeNet decimal(12,2) OUTPUT,
    @LastOrder   date          OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM Sales.Customer WHERE CustomerID = @CustomerID)
        RETURN 1;                                              -- not found

    SELECT  @Orders      = COUNT(DISTINCT o.OrderID),
            @LifetimeNet = COALESCE(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)), 0),
            @LastOrder   = MAX(o.OrderDate)
    FROM    Sales.SalesOrder     AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.CustomerID = @CustomerID
      AND   o.OrderStatus <> 'Cancelled';

    RETURN 0;
END;
GO

DECLARE @rc int, @orders int, @net decimal(12,2), @last date;

EXEC @rc = Lab.usp_GetCustomerStats @CustomerID = 12,
           @Orders = @orders OUTPUT, @LifetimeNet = @net OUTPUT, @LastOrder = @last OUTPUT;
SELECT @rc AS ReturnCode, @orders AS Orders, @net AS LifetimeNet, @last AS LastOrder;

EXEC @rc = Lab.usp_GetCustomerStats @CustomerID = 99,
           @Orders = @orders OUTPUT, @LifetimeNet = @net OUTPUT, @LastOrder = @last OUTPUT;
SELECT @rc AS ReturnCode;

Expected result: customer 12 → 0, 27, 8810.01, 2025-12-23. Customer 99 → 1. Customer 37 (exists, no orders) → 0, 0, 0.00, NULL.

Try the trap: remove OUTPUT from @Orders = @orders OUTPUT in the call. No error — @orders keeps its previous value (27), which could be silently wrong.

Worked example 3 — Several result sets

CREATE OR ALTER PROCEDURE Lab.usp_CustomerSummary
    @CustomerID int
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. Who
    SELECT CustomerID, FirstName, LastName, Email, City, Country, CustomerSegment
    FROM   Sales.Customer
    WHERE  CustomerID = @CustomerID;

    -- 2. Latest three orders
    SELECT TOP (3) OrderID, OrderDate, OrderStatus
    FROM   Sales.SalesOrder
    WHERE  CustomerID = @CustomerID
    ORDER BY OrderDate DESC, OrderID DESC;

    -- 3. Net sales by year
    SELECT  YEAR(o.OrderDate) AS OrderYear,
            COUNT(DISTINCT o.OrderID) AS Orders,
            CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS decimal(12,2)) AS NetSales
    FROM    Sales.SalesOrder AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.CustomerID = @CustomerID AND o.OrderStatus <> 'Cancelled'
    GROUP BY YEAR(o.OrderDate)
    ORDER BY OrderYear;
END;
GO

EXEC Lab.usp_CustomerSummary @CustomerID = 1;

Expected result: three grids — Alice Morgan’s details; orders 1373, 1372, 1350; and 2024: 13 orders, 4387.48 / 2025: 14 orders, 4914.22.

Worked example 4 — A data-changing procedure with an OUTPUT key

CREATE OR ALTER PROCEDURE Lab.usp_AddCustomer
    @FirstName     nvarchar(50),
    @LastName      nvarchar(50),
    @Email         nvarchar(255) = NULL,
    @City          nvarchar(60),
    @Country       nvarchar(60),
    @NewCustomerID int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    -- Validation: expected refusals return codes
    IF NULLIF(TRIM(@FirstName), N'') IS NULL OR NULLIF(TRIM(@LastName), N'') IS NULL
        RETURN 1;                                                     -- missing name
    IF @Email IS NOT NULL AND EXISTS (SELECT 1 FROM Lab.Customer WHERE Email = TRIM(@Email))
        RETURN 2;                                                     -- duplicate email

    BEGIN TRANSACTION;

    SELECT @NewCustomerID = COALESCE(MAX(CustomerID), 0) + 1
    FROM   Lab.Customer WITH (UPDLOCK, HOLDLOCK);                     -- serialise ID allocation

    INSERT INTO Lab.Customer (CustomerID, FirstName, LastName, Email, Phone, City, Country,
                              CustomerSegment, DateJoined, IsActive)
    VALUES (@NewCustomerID, TRIM(@FirstName), TRIM(@LastName), LOWER(TRIM(@Email)), NULL,
            TRIM(@City), TRIM(@Country), 'Consumer', '2026-01-02', 1);

    COMMIT TRANSACTION;
    RETURN 0;
END;
GO

DECLARE @rc int, @id int;
EXEC @rc = Lab.usp_AddCustomer @FirstName = N'Kofi', @LastName = N'Mensah',
           @Email = N' Kofi.Mensah@Example.com', @City = N'London', @Country = N'United Kingdom',
           @NewCustomerID = @id OUTPUT;
SELECT @rc AS ReturnCode, @id AS NewCustomerID;

EXEC @rc = Lab.usp_AddCustomer @FirstName = N'Kofi', @LastName = N'Mensah',
           @Email = N'kofi.mensah@example.com', @City = N'London', @Country = N'United Kingdom',
           @NewCustomerID = @id OUTPUT;
SELECT @rc AS ReturnCode;

Expected result: first call 0, 41 (email stored as kofi.mensah@example.com); second call 2 (duplicate). UPDLOCK, HOLDLOCK on the MAX read stops two simultaneous calls from taking the same ID (Day 16). Day 26 replaces this with a SEQUENCE.

Worked example 5 — A dynamic search procedure

CREATE OR ALTER PROCEDURE Lab.usp_SearchOrders
    @CustomerID int          = NULL,
    @FromDate   date         = NULL,
    @ToDate     date         = NULL,         -- exclusive
    @Status     varchar(10)  = NULL,
    @MinNet     decimal(10,2) = NULL,
    @Debug      bit          = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql nvarchar(max) = N'
SELECT  o.OrderID, o.CustomerID, o.OrderDate, o.OrderStatus,
        CAST(t.OrderNet AS decimal(10,2)) AS OrderNet
FROM    Sales.SalesOrder AS o
CROSS APPLY (SELECT SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS OrderNet
             FROM   Sales.SalesOrderLine AS l WHERE l.OrderID = o.OrderID) AS t
WHERE   1 = 1';

    IF @CustomerID IS NOT NULL SET @sql += N' AND o.CustomerID = @CustomerID';
    IF @FromDate   IS NOT NULL SET @sql += N' AND o.OrderDate >= @FromDate';
    IF @ToDate     IS NOT NULL SET @sql += N' AND o.OrderDate <  @ToDate';
    IF @Status     IS NOT NULL SET @sql += N' AND o.OrderStatus = @Status';
    IF @MinNet     IS NOT NULL SET @sql += N' AND t.OrderNet >= @MinNet';
    SET @sql += N'
ORDER BY o.OrderDate, o.OrderID;';

    IF @Debug = 1 PRINT @sql;

    EXEC sys.sp_executesql @sql,
         N'@CustomerID int, @FromDate date, @ToDate date, @Status varchar(10), @MinNet decimal(10,2)',
         @CustomerID = @CustomerID, @FromDate = @FromDate, @ToDate = @ToDate,
         @Status = @Status, @MinNet = @MinNet;
END;
GO

EXEC Lab.usp_SearchOrders @Status = 'Pending';
EXEC Lab.usp_SearchOrders @CustomerID = 1, @FromDate = '2025-12-01', @Debug = 1;
EXEC Lab.usp_SearchOrders @MinNet = 1500;

Expected result: 4 pending orders (1372–1375); customer 1’s 3 December orders (and the generated SQL in Messages, containing AND o.CustomerID = @CustomerID AND o.OrderDate >= @FromDate); 3 orders worth at least 1,500: 1014 (1577.70), 1227 (1523.00), 1312 (1574.00).

What to notice: every parameter is declared in the definition string and passed, even when unused — that is allowed and keeps the call simple. Try @Status = 'Pending'' OR 1=1 --': it returns no rows, because the whole string is compared as a value. It is never executed as code.

E. Guided practical lab — A stock-transfer procedure

Turn Day 16’s transfer script into Lab.usp_TransferStock with input validation, return codes, an OUTPUT message and the same locking strategy.

Return codes (the procedure’s contract)

Step 1 — Create the procedure

1. Do: Create it in one batch.
2. Run:

   USE RetailLab;
   GO
   
   CREATE OR ALTER PROCEDURE Lab.usp_TransferStock
       @ProductID     int,
       @FromWarehouse int,
       @ToWarehouse   int,
       @Qty           int,
       @Message       nvarchar(200) OUTPUT
   AS
   BEGIN
       SET NOCOUNT ON;
       SET XACT_ABORT ON;
   
       IF @Qty IS NULL OR @Qty <= 0 OR @FromWarehouse = @ToWarehouse
       BEGIN
           SET @Message = N'Invalid input.';
           RETURN 1;
       END;
   
       DECLARE @Available int, @Rows int;
   
       BEGIN TRANSACTION;
   
       SELECT @Available = MAX(CASE WHEN WarehouseID = @FromWarehouse THEN QuantityOnHand END),
              @Rows      = COUNT(*)
       FROM   Lab.Stock WITH (UPDLOCK, HOLDLOCK)
       WHERE  ProductID = @ProductID
         AND  WarehouseID IN (@FromWarehouse, @ToWarehouse);
   
       IF @Rows < 2
       BEGIN
           ROLLBACK TRANSACTION;
           SET @Message = N'No stock record in one of the warehouses.';
           RETURN 2;
       END;
   
       IF @Available < @Qty
       BEGIN
           ROLLBACK TRANSACTION;
           SET @Message = CONCAT(N'Only ', @Available, N' available.');
           RETURN 3;
       END;
   
       UPDATE Lab.Stock
       SET    QuantityOnHand = QuantityOnHand + CASE WHEN WarehouseID = @FromWarehouse THEN -@Qty ELSE @Qty END
       WHERE  ProductID = @ProductID
         AND  WarehouseID IN (@FromWarehouse, @ToWarehouse);
   
       COMMIT TRANSACTION;
   
       SET @Message = CONCAT(N'Transferred ', @Qty, N' unit(s) of product ', @ProductID, N'.');
       RETURN 0;
   END;
   GO

3. Observe: Commands completed successfully.
4. Why it matters: Every exit path either commits or rolls back, and sets @Message.
5. Confirm: SELECT OBJECT_ID('Lab.usp_TransferStock'); is not NULL.

Step 2 — A test harness

1. Do: Call it with several cases and collect results in a table variable.

2. Run:

   DECLARE @Results TABLE (TestCase nvarchar(40), ReturnCode int, Msg nvarchar(200));
   DECLARE @rc int, @msg nvarchar(200);
   
   EXEC @rc = Lab.usp_TransferStock 26, 2, 1, 10, @msg OUTPUT;  INSERT @Results VALUES (N'Valid transfer', @rc, @msg);
   EXEC @rc = Lab.usp_TransferStock 26, 2, 2, 10, @msg OUTPUT;  INSERT @Results VALUES (N'Same warehouse', @rc, @msg);
   EXEC @rc = Lab.usp_TransferStock 26, 2, 1, 0,  @msg OUTPUT;  INSERT @Results VALUES (N'Zero quantity', @rc, @msg);
   EXEC @rc = Lab.usp_TransferStock 3,  1, 2, 1,  @msg OUTPUT;  INSERT @Results VALUES (N'No destination record', @rc, @msg);
   EXEC @rc = Lab.usp_TransferStock 2,  2, 1, 5,  @msg OUTPUT;  INSERT @Results VALUES (N'Not enough stock', @rc, @msg);
   
   SELECT * FROM @Results;
   SELECT SUM(QuantityOnHand) AS TotalUnits, @@TRANCOUNT AS OpenTran FROM Lab.Stock;

3. Observe:

   TestCase	ReturnCode	Msg
   Valid transfer	0	Transferred 10 unit(s) of product 26.
   Same warehouse	1	Invalid input.
   Zero quantity	1	Invalid input.
   No destination record	2	No stock record in one of the warehouses.
   Not enough stock	3	Only 0 available.

   TotalUnits 1142, OpenTran 0.

4. Why it matters: A small harness documents the contract and catches regressions when the procedure changes. Positional arguments are used here for compactness; in application code, prefer named ones.

5. Confirm: the five rows and the invariant.

Step 3 — Grant execute, not table access (demonstration)

1. Do: Create a test user without a login, grant only EXECUTE, and impersonate it.
2. Run:

   CREATE USER StockClerk WITHOUT LOGIN;
   GRANT EXECUTE ON Lab.usp_TransferStock TO StockClerk;
   
   EXECUTE AS USER = 'StockClerk';
       DECLARE @rc int, @msg nvarchar(200);
       EXEC @rc = Lab.usp_TransferStock 26, 1, 2, 1, @msg OUTPUT;
       SELECT @rc AS ReturnCode, @msg AS Msg;
       SELECT TOP (1) * FROM Lab.Stock;           -- direct access
   REVERT;
   
   DROP USER StockClerk;

3. Observe: the procedure succeeds (0, Transferred 1 unit(s)…); the direct SELECT fails: The SELECT permission was denied on the object ‘Stock’, database ‘RetailLab’, schema ‘Lab’.
4. Why it matters: Ownership chaining: the clerk can do exactly what the procedure allows and nothing else.
5. Confirm: after REVERT, SELECT USER_NAME(); returns dbo.

F. Independent practice

Task 1 — Product sales for a year

Create Lab.usp_ProductSalesByYear @Year int, @Top int = 10 returning the top @Top products by net sales in that year (non-cancelled): ProductCode, ProductName, Units, NetSales. Execute it for 2025 with @Top = 3.

Success criteria: TNT-002 (26 units, 8777.35), TNT-003 (22, 5241.45), SLP-002 (17, 4903.60).

Task 2 — Stats with validation

Extend Lab.usp_GetCustomerStats so that a NULL @CustomerID returns code 9 and sets all outputs to NULL, and so that a customer with no orders returns code 4 (while still setting @Orders = 0). Test customers NULL, 12, 37 and 99.

Success criteria: NULL → 9; 12 → 0 (27, 8810.01, 2025-12-23); 37 → 4 (0, 0.00, NULL); 99 → 1.

Task 3 — Search with a sort option (safely)

Add a parameter @SortBy varchar(10) = 'Date' to Lab.usp_SearchOrders accepting Date, Net or Customer. Sorting can’t be parameterised as a value, so build the ORDER BY from a whitelist.

Success criteria: @MinNet = 1500, @SortBy = 'Net' returns 1014, 1312, 1227 (descending net); an unknown @SortBy such as 'x; DROP TABLE' falls back to date order (or returns code 1 — your choice, documented).

Task 4 — Monthly sales with INSERT … EXEC (challenging)

Create Lab.usp_MonthlySales @Year int returning MonthNo, Orders, NetSales for each month with sales. In a separate batch, capture its output for 2025 with INSERT INTO #Monthly EXEC …, then compute a year-to-date column in the caller.

Success criteria: 12 rows; January 3749.57 (YTD 3749.57); December 9718.65 (YTD 63741.94).

Stretch challenge — A procedure that calls a procedure

Write Lab.usp_RebalanceProduct @ProductID int, @Message nvarchar(400) OUTPUT that moves stock so both warehouses hold as equal a quantity as possible (the source keeps the extra unit if the total is odd), by calling Lab.usp_TransferStock. Return its code. Test with products 26 and 2.

Success criteria: reset Lab.Stock first. Product 26 (18 / 40) → moves 11 from warehouse 2: result 29 / 29. Product 2 (25 / 0) → moves 12 from warehouse 1: result 13 / 12. Total stays 1142.

G. Solutions and explanations

CREATE OR ALTER PROCEDURE Lab.usp_ProductSalesByYear
    @Year int,
    @Top  int = 10
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (@Top)
            p.ProductCode, p.ProductName,
            SUM(l.Quantity) AS Units,
            CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS decimal(12,2)) AS NetSales
    FROM    Sales.SalesOrder AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    JOIN    Inventory.Product    AS p ON p.ProductID = l.ProductID
    WHERE   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= DATEFROMPARTS(@Year, 1, 1)
      AND   o.OrderDate <  DATEFROMPARTS(@Year + 1, 1, 1)
    GROUP BY p.ProductCode, p.ProductName
    ORDER BY NetSales DESC, p.ProductCode;
END;
GO

EXEC Lab.usp_ProductSalesByYear @Year = 2025, @Top = 3;

CREATE OR ALTER PROCEDURE Lab.usp_GetCustomerStats
    @CustomerID  int,
    @Orders      int           OUTPUT,
    @LifetimeNet decimal(12,2) OUTPUT,
    @LastOrder   date          OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @Orders = NULL, @LifetimeNet = NULL, @LastOrder = NULL;

    IF @CustomerID IS NULL RETURN 9;
    IF NOT EXISTS (SELECT 1 FROM Sales.Customer WHERE CustomerID = @CustomerID) RETURN 1;

    SELECT  @Orders      = COUNT(DISTINCT o.OrderID),
            @LifetimeNet = COALESCE(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)), 0),
            @LastOrder   = MAX(o.OrderDate)
    FROM    Sales.SalesOrder AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.CustomerID = @CustomerID AND o.OrderStatus <> 'Cancelled';

    IF @Orders = 0 RETURN 4;
    RETURN 0;
END;
GO

-- Inside Lab.usp_SearchOrders, add the parameter  @SortBy varchar(10) = 'Date'
-- and replace the ORDER BY line with:
    SET @sql += CASE @SortBy
                    WHEN 'Net'      THEN N' ORDER BY t.OrderNet DESC, o.OrderID;'
                    WHEN 'Customer' THEN N' ORDER BY o.CustomerID, o.OrderDate, o.OrderID;'
                    ELSE                 N' ORDER BY o.OrderDate, o.OrderID;'
                END;

CREATE OR ALTER PROCEDURE Lab.usp_MonthlySales
    @Year int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT  MONTH(o.OrderDate) AS MonthNo,
            COUNT(DISTINCT o.OrderID) AS Orders,
            CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS decimal(12,2)) AS NetSales
    FROM    Sales.SalesOrder AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= DATEFROMPARTS(@Year, 1, 1) AND o.OrderDate < DATEFROMPARTS(@Year + 1, 1, 1)
    GROUP BY MONTH(o.OrderDate)
    ORDER BY MonthNo;
END;
GO

CREATE TABLE #Monthly (MonthNo int, Orders int, NetSales decimal(12,2));
INSERT INTO #Monthly (MonthNo, Orders, NetSales)
EXEC Lab.usp_MonthlySales @Year = 2025;

SELECT MonthNo, Orders, NetSales,
       SUM(NetSales) OVER (ORDER BY MonthNo ROWS UNBOUNDED PRECEDING) AS YTD
FROM   #Monthly
ORDER BY MonthNo;

DROP TABLE #Monthly;

CREATE OR ALTER PROCEDURE Lab.usp_RebalanceProduct
    @ProductID int,
    @Message   nvarchar(400) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @q1 int, @q2 int, @move int, @from int, @to int, @rc int, @msg nvarchar(200);

    SELECT @q1 = MAX(CASE WHEN WarehouseID = 1 THEN QuantityOnHand END),
           @q2 = MAX(CASE WHEN WarehouseID = 2 THEN QuantityOnHand END)
    FROM   Lab.Stock
    WHERE  ProductID = @ProductID;

    IF @q1 IS NULL OR @q2 IS NULL
    BEGIN
        SET @Message = N'Product is not stocked in both warehouses.';
        RETURN 2;
    END;

    SELECT @from = CASE WHEN @q1 >= @q2 THEN 1 ELSE 2 END,
           @to   = CASE WHEN @q1 >= @q2 THEN 2 ELSE 1 END,
           @move = ABS(@q1 - @q2) / 2;

    IF @move = 0
    BEGIN
        SET @Message = N'Already balanced.';
        RETURN 0;
    END;

    EXEC @rc = Lab.usp_TransferStock @ProductID = @ProductID, @FromWarehouse = @from,
               @ToWarehouse = @to, @Qty = @move, @Message = @msg OUTPUT;

    SET @Message = CONCAT(N'Rebalance: ', @msg);
    RETURN @rc;
END;
GO

DECLARE @rc int, @m nvarchar(400);
EXEC @rc = Lab.usp_RebalanceProduct @ProductID = 26, @Message = @m OUTPUT;  SELECT @rc, @m;
EXEC @rc = Lab.usp_RebalanceProduct @ProductID = 2,  @Message = @m OUTPUT;  SELECT @rc, @m;
SELECT ProductID, WarehouseID, QuantityOnHand FROM Lab.Stock WHERE ProductID IN (2, 26) ORDER BY ProductID, WarehouseID;

H. Knowledge check

1. Understanding. Name three ways a procedure can return information, and say what each is best for.

2. Debugging. A caller writes EXEC Lab.usp_GetCustomerStats @CustomerID = 12, @Orders = @o, @LifetimeNet = @n, @LastOrder = @d; and finds @o is NULL. Why?

3. Query interpretation. A search procedure builds N'… WHERE o.OrderStatus = ''' + @Status + N'''' and runs it with EXEC(@sql). What is wrong, and how do you fix it?

4. Understanding. A user has EXECUTE on Lab.usp_TransferStock but no permissions on Lab.Stock. Why does the procedure still work — and when would it stop working?

5. Debugging. CREATE OR ALTER PROCEDURE Lab.usp_X AS SELECT 1; EXEC Lab.usp_X; (on one line, no GO) creates a procedure that calls itself. Why?

I. Troubleshooting and cleanup

Cleanup. Keep Lab.usp_TransferStock, Lab.usp_AddCustomer and the Lab.Stock/Lab.Customer copies — Day 20 extends them (its setup recreates them anyway). Remove the rest if you like:

DROP PROCEDURE IF EXISTS Lab.usp_GetCustomerOrders, Lab.usp_GetCustomerStats, Lab.usp_CustomerSummary,
                         Lab.usp_SearchOrders, Lab.usp_ProductSalesByYear, Lab.usp_MonthlySales,
                         Lab.usp_RebalanceProduct;
IF USER_ID('StockClerk') IS NOT NULL DROP USER StockClerk;

Core data is unchanged.

J. Completion checklist, recap and references

Completion checklist

• Worked example 2 returned 0, 27, 8810.01, 2025-12-23 for customer 12 and 1 for customer 99.
• Worked example 5’s injection attempt returned no rows.
• Your transfer harness produced codes 0, 1, 1, 2, 3 with the total still 1142.
• The StockClerk user could run the procedure but not read the table.
• Tasks 1–4 match the success criteria.

Recap

• CREATE OR ALTER PROCEDURE schema.name in its own batch; SET NOCOUNT ON; call with named parameters.
• Defaults make parameters optional; OUTPUT must appear in both declaration and call; RETURN n gives a status code (0 = success).
• Data-changing procedures: validate, SET XACT_ABORT ON, transaction, set-based work, commit, report.
• Dynamic SQL in procedures: build only the needed clauses, pass values via sp_executesql parameters, whitelist identifiers.
• GRANT EXECUTE plus ownership chaining gives controlled access; dynamic SQL breaks the chain.

Next: Day 18 — User-defined functions: scalar, inline table-valued and multi-statement table-valued functions, and their performance differences.

References (Microsoft Learn)

• CREATE PROCEDURE (Transact-SQL)
• EXECUTE (Transact-SQL)
• Return data from a stored procedure
• Control-of-flow (Transact-SQL)
• sp_executesql (Transact-SQL)
• Tutorial: Ownership chains and context switching
• Authorization and permissions in SQL Server
• SET NOCOUNT (Transact-SQL)