SQL Stored Procedures and Functions: Complete Guide

-- PostgreSQL: Create a basic stored procedure
CREATE OR REPLACE PROCEDURE get_customer_orders(
  customer_id_param INT
)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT o.order_id, o.order_date, o.total
  FROM orders o
  WHERE o.customer_id = customer_id_param
  ORDER BY o.order_date DESC;
END;
$$;

-- Call the procedure
CALL get_customer_orders(123);

-- SQL Server version
CREATE PROCEDURE get_customer_orders
  @customer_id_param INT
AS
BEGIN
  SELECT o.order_id, o.order_date, o.total
  FROM orders o
  WHERE o.customer_id = @customer_id_param
  ORDER BY o.order_date DESC;
END;

-- Call in SQL Server
EXEC get_customer_orders @customer_id_param = 123;

-- PostgreSQL: Get top products for a category
CREATE OR REPLACE FUNCTION get_top_products(
  category_name_param VARCHAR,
  limit_param INT DEFAULT 10
)
RETURNS TABLE (
  product_id INT,
  product_name VARCHAR,
  price DECIMAL,
  total_sales DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    p.product_id,
    p.product_name,
    p.price,
    COALESCE(SUM(oi.quantity * oi.price), 0) as total_sales
  FROM products p
  LEFT JOIN order_items oi ON p.product_id = oi.product_id
  WHERE p.category = category_name_param
  GROUP BY p.product_id, p.product_name, p.price
  ORDER BY total_sales DESC
  LIMIT limit_param;
END;
$$;

-- Use like a regular table
SELECT * FROM get_top_products('Electronics', 5);

-- Join with other tables
SELECT t.*, c.customer_name
FROM get_top_products('Electronics', 5) t
JOIN customers c ON c.preferred_category = 'Electronics';

-- PostgreSQL: Apply discount based on customer tier
CREATE OR REPLACE PROCEDURE apply_discount(
  order_id_param INT
)
LANGUAGE plpgsql
AS $$
DECLARE
  customer_tier VARCHAR;
  discount_rate DECIMAL;
  current_total DECIMAL;
BEGIN
  -- Get customer tier and current total
  SELECT c.tier, o.total
  INTO customer_tier, current_total
  FROM orders o
  JOIN customers c ON o.customer_id = c.customer_id
  WHERE o.order_id = order_id_param;

  -- Determine discount based on tier
  IF customer_tier = 'GOLD' THEN
    discount_rate := 0.15;  -- 15% discount
  ELSIF customer_tier = 'SILVER' THEN
    discount_rate := 0.10;  -- 10% discount
  ELSIF customer_tier = 'BRONZE' THEN
    discount_rate := 0.05;  -- 5% discount
  ELSE
    discount_rate := 0.00;  -- No discount
  END IF;

  -- Apply the discount
  IF discount_rate > 0 THEN
    UPDATE orders
    SET total = current_total * (1 - discount_rate),
        discount_applied = discount_rate
    WHERE order_id = order_id_param;

    RAISE NOTICE 'Applied % discount to order %', discount_rate, order_id_param;
  END IF;
END;
$$;

-- PostgreSQL: Transfer money with transaction control
CREATE OR REPLACE PROCEDURE transfer_money(
  from_account_id INT,
  to_account_id INT,
  amount DECIMAL,
  OUT success BOOLEAN,
  OUT message VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
  from_balance DECIMAL;
BEGIN
  -- Start transaction (implicit in PostgreSQL procedures)

  -- Check source account balance
  SELECT balance INTO from_balance
  FROM accounts
  WHERE account_id = from_account_id
  FOR UPDATE;  -- Lock the row

  IF from_balance < amount THEN
    success := FALSE;
    message := 'Insufficient funds';
    ROLLBACK;
    RETURN;
  END IF;

  -- Debit from source account
  UPDATE accounts
  SET balance = balance - amount
  WHERE account_id = from_account_id;

  -- Credit to destination account
  UPDATE accounts
  SET balance = balance + amount
  WHERE account_id = to_account_id;

  -- Log the transfer
  INSERT INTO transfer_log (from_account, to_account, amount, transfer_date)
  VALUES (from_account_id, to_account_id, amount, CURRENT_TIMESTAMP);

  COMMIT;
  success := TRUE;
  message := 'Transfer successful';

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    success := FALSE;
    message := 'Transfer failed: ' || SQLERRM;
END;
$$;