Stored Procedures & Functions

What are Stored Procedures?

Saved SQL code that you can run anytime with one command.

Think of it like: A recipe saved in a cookbook. Write once, cook many times.

Creating a Procedure

CREATE PROCEDURE procedure_name(parameters)
AS
BEGIN
  -- SQL statements here
END;

Example: Sell Product

Without procedure (repeat every time):

UPDATE products SET stock = stock - 1 WHERE id = 101;
UPDATE sales SET count = count + 1 WHERE product_id = 101;

With procedure (write once):

CREATE PROCEDURE sell_product(p_id INT)
AS
BEGIN
  UPDATE products SET stock = stock - 1 WHERE id = p_id;
  UPDATE sales SET count = count + 1 WHERE product_id = p_id;
END;

Use it:

CALL sell_product(101);
CALL sell_product(102);

Functions vs Procedures

-- Function: Returns a value
CREATE FUNCTION get_total(order_id INT) RETURNS DECIMAL
AS
BEGIN
  RETURN (SELECT SUM(amount) FROM items WHERE order = order_id);
END;

-- Use in queries
SELECT get_total(1);

Summary

Procedure: Saved SQL code, called with CALL
Function: Returns a value, used in queries
Write once, reuse many times