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