Stored Procedures

Stored procedures are precompiled SQL statements stored in the database that can be executed repeatedly.

Benefits of Stored Procedures

1. Performance: Precompiled and cached
2. Security: Control access to data
3. Maintainability: Centralized logic
4. Reduced Network Traffic: Execute multiple statements in one call

Creating a Stored Procedure

CREATE PROCEDURE GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SELECT o.order_id, o.order_date, o.total_amount
    FROM orders o
    WHERE o.customer_id = @CustomerId
    ORDER BY o.order_date DESC;
END;

Executing a Stored Procedure

EXEC GetCustomerOrders @CustomerId = 1;

Stored Procedure with Output

CREATE PROCEDURE GetOrderTotal
    @OrderId INT,
    @Total DECIMAL(10,2) OUTPUT
AS
BEGIN
    SELECT @Total = total_amount
    FROM orders
    WHERE order_id = @OrderId;
END;

Stored procedures are powerful tools for database development!