What Is a Stored Procedure?

Once created, a stored procedure's SQL is parsed and its execution plan is cached by the database engine. Subsequent calls reuse the cached plan, avoiding re-parsing and re-optimization — a significant performance benefit for complex queries.

-- Create a simple stored procedure
CREATE PROCEDURE GetActiveCustomers
AS
BEGIN
    SELECT customer_id, name, email, phone
    FROM customers
    WHERE status = 'ACTIVE'
    ORDER BY name;
END;

-- Execute the procedure
EXEC GetActiveCustomers;

Creating Procedures with Parameters

-- Procedure with input and output parameters
CREATE PROCEDURE GetCustomerOrderSummary
    @customer_id INT,
    @total_orders INT OUTPUT,
    @total_spent DECIMAL(10,2) OUTPUT
AS
BEGIN
    SELECT
        @total_orders = COUNT(*),
        @total_spent = ISNULL(SUM(total), 0)
    FROM orders
    WHERE customer_id = @customer_id;
END;

-- Call with output parameters
DECLARE @orders INT, @spent DECIMAL(10,2);
EXEC GetCustomerOrderSummary
    @customer_id = 42,
    @total_orders = @orders OUTPUT,
    @total_spent = @spent OUTPUT;

SELECT @orders AS TotalOrders, @spent AS TotalSpent;

Default Parameter Values

Parameters can have default values, making them optional when calling the procedure.

CREATE PROCEDURE SearchProducts
    @category NVARCHAR(50) = NULL,
    @min_price DECIMAL(10,2) = 0,
    @max_price DECIMAL(10,2) = 999999,
    @sort_by NVARCHAR(20) = 'name'
AS
BEGIN
    SELECT product_id, name, category, price
    FROM products
    WHERE (@category IS NULL OR category = @category)
      AND price BETWEEN @min_price AND @max_price
    ORDER BY
        CASE @sort_by
            WHEN 'name' THEN name
            WHEN 'price' THEN CAST(price AS NVARCHAR)
        END;
END;

-- Call with various parameter combinations
EXEC SearchProducts;                          -- all products
EXEC SearchProducts @category = 'Electronics'; -- electronics only
EXEC SearchProducts @min_price = 50, @max_price = 200;
EXEC SearchProducts @category = 'Books', @sort_by = 'price';

Control Flow in Procedures

Procedures support conditional logic and loops for complex business rules.

CREATE PROCEDURE ProcessOrder
    @order_id INT
AS
BEGIN
    DECLARE @status NVARCHAR(20);
    DECLARE @total DECIMAL(10,2);

    SELECT @status = status, @total = total
    FROM orders WHERE order_id = @order_id;

    IF @status = 'CANCELLED'
    BEGIN
        PRINT 'Order is already cancelled.';
        RETURN;
    END

    IF @total > 1000
    BEGIN
        UPDATE orders
        SET status = 'PENDING_APPROVAL',
            discount = @total * 0.10
        WHERE order_id = @order_id;

        INSERT INTO approval_queue (order_id, requested_by, request_date)
        VALUES (@order_id, SYSTEM_USER, GETDATE());
    END
    ELSE
    BEGIN
        UPDATE orders
        SET status = 'CONFIRMED'
        WHERE order_id = @order_id;

        INSERT INTO order_log (order_id, action, action_date)
        VALUES (@order_id, 'AUTO_CONFIRMED', GETDATE());
    END
END;

Modifying and Dropping Procedures

-- Alter an existing procedure
ALTER PROCEDURE GetActiveCustomers
    @min_order_count INT = 0
AS
BEGIN
    SELECT c.customer_id, c.name, c.email,
           COUNT(o.order_id) AS order_count
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    WHERE c.status = 'ACTIVE'
    GROUP BY c.customer_id, c.name, c.email
    HAVING COUNT(o.order_id) >= @min_order_count
    ORDER BY order_count DESC;
END;

-- Drop a procedure
DROP PROCEDURE IF EXISTS GetActiveCustomers;

-- Check if a procedure exists
IF OBJECT_ID('GetActiveCustomers', 'P') IS NOT NULL
    PRINT 'Procedure exists';
ELSE
    PRINT 'Procedure does not exist';

Error Handling in Procedures

CREATE PROCEDURE TransferFunds
    @from_account INT,
    @to_account INT,
    @amount DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Validate sufficient balance
        DECLARE @balance DECIMAL(10,2);
        SELECT @balance = balance FROM accounts WHERE id = @from_account;

        IF @balance < @amount
        BEGIN
            RAISERROR('Insufficient funds. Available: %.2f', 16, 1, @balance);
            RETURN;
        END

        UPDATE accounts SET balance = balance - @amount WHERE id = @from_account;
        UPDATE accounts SET balance = balance + @amount WHERE id = @to_account;

        INSERT INTO transfers (from_id, to_id, amount, transfer_date)
        VALUES (@from_account, @to_account, @amount, GETDATE());

        COMMIT;
        PRINT 'Transfer completed successfully.';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK;

        DECLARE @msg NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @sev INT = ERROR_SEVERITY();
        RAISERROR(@msg, @sev, 1);
    END CATCH
END;

Advantages Over Inline SQL