Stored procedures are reusable SQL code blocks saved in the database. They improve performance, enhance security, and centralize business logic for easier maintenance.

Creating a Simple Procedure

CREATE PROCEDURE GetCustomerOrders(@customer_id INT)
AS
BEGIN
    SELECT order_id, order_date, total
    FROM orders
    WHERE customer_id = @customer_id
    ORDER BY order_date DESC;
END;

Executing a Stored Procedure

-- Call the procedure
EXEC GetCustomerOrders @customer_id = 123;

Procedure with Multiple Parameters

CREATE PROCEDURE AddNewProduct(
    @product_name VARCHAR(100),
    @price DECIMAL(10,2),
    @category_id INT
)
AS
BEGIN
    INSERT INTO products (name, price, category_id, created_date)
    VALUES (@product_name, @price, @category_id, GETDATE());

    -- Return the new product ID
    SELECT SCOPE_IDENTITY() AS new_product_id;
END;

Procedure with Logic

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

    -- Calculate order total
    SELECT @total = SUM(quantity * price)
    FROM order_items
    WHERE order_id = @order_id;

    -- Update order
    UPDATE orders
    SET total = @total, status = 'processed'
    WHERE order_id = @order_id;

    -- Log the action
    INSERT INTO order_log (order_id, action, timestamp)
    VALUES (@order_id, 'Processed', GETDATE());
END;

When to Use Stored Procedures

• Complex business logic executed frequently
• Operations requiring multiple SQL statements
• Improved security (grant execute permission only)
• Better performance through query plan caching