Chapter 10: Stored Procedures

A stored procedure is a named collection of SQL statements saved in the database and executed on demand. Instead of sending the same complex query from an application every time, you store the logic once and call it by name. The database compiles the procedure on first execution and caches the execution plan, which makes repeated calls faster than submitting raw SQL each time.

---

10.1 Creating a Stored Procedure

CREATE PROCEDURE schema_name.procedure_name
    @parameter1 data_type,
    @parameter2 data_type
AS
BEGIN
    -- SQL statements
END;

CREATE PROC is shorthand for CREATE PROCEDURE. Both work identically.

A simple procedure that returns all products:

CREATE PROCEDURE production.uspProductList
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM production.products
    ORDER BY product_name;
END;

---

10.2 Executing a Stored Procedure

EXEC production.uspProductList;
-- or
EXECUTE production.uspProductList;

product_name                  | list_price
--------------------------------+-----------
Electra Amsterdam Original 3i   | 1049.99
Heller Shagamaw Frame           | 1320.99
Trek 820 - 2016                  | 379.99

EXEC and EXECUTE are identical. The short form EXEC is standard practice.

---

10.3 Input Parameters

Parameters make a procedure reusable across different inputs. Each parameter is prefixed with @.

CREATE PROCEDURE production.uspFindProducts
    @min_price DECIMAL(10, 2),
    @max_price DECIMAL(10, 2)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM production.products
    WHERE list_price BETWEEN @min_price AND @max_price
    ORDER BY list_price;
END;

Call it with positional arguments:

EXEC production.uspFindProducts 100, 500;

Or with named arguments (safer and clearer):

EXEC production.uspFindProducts
    @min_price = 100,
    @max_price = 500;

product_name              | list_price
----------------------------+-----------
Trek 820 - 2016               | 379.99
Electra Townie Original 7D    | 489.99
Surly Wednesday Frameset      | 499.99

---

10.4 Default Parameter Values

Parameters can have defaults so callers do not have to supply every argument.

CREATE PROCEDURE production.uspFindProducts
    @min_price DECIMAL(10, 2) = 0,
    @max_price DECIMAL(10, 2) = 999999
AS
BEGIN
    SELECT product_name, list_price
    FROM production.products
    WHERE list_price BETWEEN @min_price AND @max_price
    ORDER BY list_price;
END;

Now all three calls work:

EXEC production.uspFindProducts;                    -- uses both defaults
EXEC production.uspFindProducts @min_price = 500;   -- uses default max
EXEC production.uspFindProducts 100, 500;           -- both supplied

---

10.5 Variables

Variables store intermediate values inside a procedure. They are declared with DECLARE and assigned with SET or SELECT.

CREATE PROCEDURE production.uspGetProductList
    @model_year SMALLINT
AS
BEGIN
    DECLARE @product_list VARCHAR(MAX);
    SET @product_list = '';

    SELECT
        @product_list = @product_list + product_name + CHAR(10)
    FROM production.products
    WHERE model_year = @model_year
    ORDER BY product_name;

    PRINT @product_list;
END;

EXEC production.uspGetProductList 2018;

Electra Townie Commute Go! - 2018
Haro Flightline One ST - 2018
Trek Domane SLR 9 Disc - 2018

CHAR(10) inserts a line break between names. PRINT outputs to the messages tab in SSMS.

---

10.6 OUTPUT Parameters

An output parameter lets the procedure return a value back to the caller.

CREATE PROCEDURE production.uspFindProductsCount
    @min_price    DECIMAL(10, 2) = 0,
    @max_price    DECIMAL(10, 2) = 999999,
    @product_count INT OUTPUT
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM production.products
    WHERE list_price BETWEEN @min_price AND @max_price
    ORDER BY list_price;

    SELECT @product_count = @@ROWCOUNT;
END;

The caller must declare a variable, pass it with the OUTPUT keyword, and read it after execution:

DECLARE @count INT;

EXEC production.uspFindProductsCount
    @min_price    = 100,
    @max_price    = 500,
    @product_count = @count OUTPUT;

SELECT @count AS products_found;

products_found
--------------
14

@@ROWCOUNT is a system variable that holds the number of rows affected or returned by the most recent statement.

Input vs Output Parameters

Input  (@param data_type)         - value flows IN to the procedure
Output (@param data_type OUTPUT)  - value flows OUT from the procedure

---

10.7 Error Handling in Procedures

Use TRY/CATCH inside a stored procedure to handle errors cleanly.

CREATE PROCEDURE sales.uspInsertOrder
    @customer_id INT,
    @store_id    INT,
    @staff_id    INT,
    @order_id    INT OUTPUT
AS
BEGIN
    BEGIN TRY
        INSERT INTO sales.orders (customer_id, order_status, order_date, store_id, staff_id)
        VALUES (@customer_id, 1, GETDATE(), @store_id, @staff_id);

        SET @order_id = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        SET @order_id = -1;
        THROW;
    END CATCH;
END;

SCOPE_IDENTITY() returns the last identity value inserted in the current scope. If the INSERT fails, @order_id is set to -1 and the error is re-thrown to the caller with THROW.

---

10.8 Modifying a Stored Procedure

ALTER PROCEDURE replaces the procedure body without changing permissions.

ALTER PROCEDURE production.uspFindProducts
    @min_price DECIMAL(10, 2) = 0,
    @max_price DECIMAL(10, 2) = 999999,
    @category_id INT = NULL
AS
BEGIN
    SELECT product_name, list_price
    FROM production.products
    WHERE list_price BETWEEN @min_price AND @max_price
      AND (@category_id IS NULL OR category_id = @category_id)
    ORDER BY list_price;
END;

The (@category_id IS NULL OR category_id = @category_id) pattern is a standard way to make a filter optional. If @category_id is not passed, the condition is always true and all categories are returned.

---

10.9 Dropping a Stored Procedure

DROP PROCEDURE production.uspFindProducts;

-- Drop if it exists (SQL Server 2016+)
DROP PROCEDURE IF EXISTS production.uspFindProducts;

---

10.10 Stored Procedure vs Ad-Hoc SQL

	Ad-Hoc SQL	Stored Procedure
Reuse	Copy-paste required	Call by name
Execution plan	Compiled each run	Cached after first run
Security	Permissions on tables	Permissions on procedure only
Maintainability	Changes require app deployment	Change logic without app deploy
Parameterization	Client must sanitize input	Parameters prevent SQL injection
Debugging	Harder	Easier - isolated unit

Interview Angle "What are the advantages of stored procedures over ad-hoc SQL?" The key points: execution plan caching (faster on repeated calls), security (grant EXEC permission without exposing the underlying tables), maintainability (change logic in one place), and SQL injection prevention through parameterization.

---

10.11 Putting It Together

A reporting procedure that returns order statistics for a given store and date range, with an output parameter for the total row count.

CREATE PROCEDURE sales.uspOrderSummary
    @store_id   INT,
    @start_date DATE,
    @end_date   DATE,
    @order_count INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        o.order_id,
        c.first_name + ' ' + c.last_name AS customer_name,
        o.order_date,
        o.order_status,
        SUM(oi.quantity * oi.list_price * (1 - oi.discount)) AS order_total
    FROM sales.orders AS o
    INNER JOIN sales.customers AS c
        ON o.customer_id = c.customer_id
    INNER JOIN sales.order_items AS oi
        ON o.order_id = oi.order_id
    WHERE o.store_id = @store_id
      AND o.order_date BETWEEN @start_date AND @end_date
    GROUP BY
        o.order_id,
        c.first_name,
        c.last_name,
        o.order_date,
        o.order_status
    ORDER BY o.order_date DESC;

    SET @order_count = @@ROWCOUNT;
END;

DECLARE @total INT;

EXEC sales.uspOrderSummary
    @store_id    = 1,
    @start_date  = '2018-01-01',
    @end_date    = '2018-12-31',
    @order_count = @total OUTPUT;

SELECT @total AS orders_in_period;

order_id | customer_name   | order_date  | order_status | order_total
---------+-----------------+-------------+--------------+------------
1542     | Daniel Cho      | 2018-12-19  | 1            | 1049.99
1538     | Priya Nair      | 2018-12-14  | 4            | 2899.99

orders_in_period
----------------
214

SET NOCOUNT ON suppresses the "N rows affected" message that SQL Server normally sends after each DML statement inside a procedure. This reduces network traffic and keeps the output clean when calling from an application.

---

Summary

A stored procedure is a named set of SQL statements stored in the database and called with EXEC. Input parameters accept values from the caller. Output parameters return values back. Variables declared with DECLARE store intermediate results inside the procedure. Default parameter values make arguments optional. TRY/CATCH handles errors cleanly, and THROW re-raises them to the caller. ALTER PROCEDURE modifies logic without dropping permissions. SCOPE_IDENTITY() returns the last inserted identity value. SET NOCOUNT ON suppresses row-count messages for cleaner application output. Stored procedures improve reuse, security, and performance compared to repeated ad-hoc SQL.

---

Exercises

10.1 - Create a stored procedure production.uspProductsByCategory that accepts a @category_id INT parameter and returns all products in that category ordered by list_price descending.

10.2 - Modify the procedure from 13.1 to add a second optional parameter @max_price DECIMAL(10,2) = NULL that, when supplied, further filters to products at or below that price.

10.3 - Create a stored procedure that inserts a new customer into sales.customers and returns the new customer_id via an output parameter.

10.4 - The following procedure compiles but produces unexpected results. Identify the issue:

CREATE PROCEDURE sales.uspCustomerOrders @city VARCHAR(50)
AS
BEGIN
    SELECT o.order_id, o.order_date
    FROM sales.orders o
    INNER JOIN sales.customers c ON o.customer_id = c.customer_id
    WHERE city = @city;
END;

10.5 - Create a stored procedure sales.uspMonthlyRevenue that accepts @year INT and @month INT and returns total revenue for that month across all stores. Include a TRY/CATCH block that returns 0 as revenue if any error occurs.

10.6 - Think About It: A developer argues that storing business logic in stored procedures makes the application harder to maintain because the logic is split between the database and the application code. Another developer argues the opposite: stored procedures centralize logic, making it easier. Under what circumstances is each person correct, and how would you decide where to put the logic for a new feature?