Stored Procedures and Functions
Write reusable SQL code with stored procedures and functions.
Introduction to Stored Procedures
Imagine you need to calculate employee bonuses based on complex business rules - performance rating, department, years of service, etc. You could write this logic in your application code, but what if multiple applications need it? What if the rules change?
Stored procedures let you save SQL code directly in the database, making it reusable, maintainable, and secure. Instead of sending complex queries from your application each time, you call a stored procedure by name.
Benefits:
- Reusability: Write once, call from anywhere (multiple apps, reports, triggers)
- Performance: Pre-compiled and optimized by the database
- Security: Grant execute permission without exposing table structure
- Maintainability: Update logic in one place
- Reduced network traffic: Send procedure name instead of full SQL
Real-world scenario: A stored procedure calculate_order_total encapsulates discount logic, tax calculations, and shipping fees. Every application just calls the procedure - no need to duplicate complex business logic.
A named collection of SQL statements stored in the database that can accept parameters, perform operations, and return results. Think of it as a function or method that lives in your database.
Feature | Regular Query | Stored Procedure |
|---|---|---|
| Location | Application code | Database |
| Reusability | Copy-paste across apps | Call by name |
| Performance | Parsed each time | Pre-compiled |
| Security | Needs table permissions | Execute permission only |
| Maintenance | Update in all apps | Update once in DB |
Creating Your First Stored Procedure
Basic syntax (MySQL/SQL Server):
DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
-- SQL statements here
END //
DELIMITER ;DELIMITER changes the statement terminator from ; to // temporarily, so the procedure body can contain semicolons.
Simple example: Get all employees
id | name | department | salary |
|---|---|---|---|
| 1 | Alice | Engineering | 95000 |
| 2 | Bob | Sales | 72000 |
| 3 | Charlie | Engineering | 80000 |
| 4 | Diana | Marketing | 68000 |
DELIMITER //
CREATE PROCEDURE get_all_employees()
BEGIN
SELECT id, name, department, salary
FROM employees
ORDER BY name;
END //
DELIMITER ;Calling the procedure:
CALL get_all_employees();PostgreSQL syntax (slightly different):
CREATE OR REPLACE PROCEDURE get_all_employees()
LANGUAGE SQL
AS $$
SELECT id, name, department, salary
FROM employees
ORDER BY name;
$$;
-- Call it
CALL get_all_employees();Database differences: MySQL uses DELIMITER, PostgreSQL uses $$, SQL Server uses GO. The core concepts are the same, but syntax varies slightly.
Parameters - IN, OUT, INOUT
Parameters make procedures flexible by accepting input and returning output.
Type | Purpose | Example Use |
|---|---|---|
| IN | Input only (default) | Pass employee ID to query |
| OUT | Output only | Return calculated total |
| INOUT | Both input and output | Pass value, get modified value back |
IN parameters (input):
DELIMITER //
CREATE PROCEDURE get_employees_by_dept(IN dept_name VARCHAR(50))
BEGIN
SELECT id, name, salary
FROM employees
WHERE department = dept_name
ORDER BY salary DESC;
END //
DELIMITER ;CALL get_employees_by_dept('Engineering');OUT parameters (output):
DELIMITER //
CREATE PROCEDURE get_employee_count(
IN dept_name VARCHAR(50),
OUT emp_count INT
)
BEGIN
SELECT COUNT(*) INTO emp_count
FROM employees
WHERE department = dept_name;
END //
DELIMITER ;-- Call and retrieve output
CALL get_employee_count('Engineering', @count);
SELECT @count AS employee_count;Multiple parameters:
DELIMITER //
CREATE PROCEDURE get_salary_stats(
IN dept_name VARCHAR(50),
OUT avg_salary DECIMAL(10,2),
OUT max_salary DECIMAL(10,2),
OUT min_salary DECIMAL(10,2)
)
BEGIN
SELECT
AVG(salary),
MAX(salary),
MIN(salary)
INTO avg_salary, max_salary, min_salary
FROM employees
WHERE department = dept_name;
END //
DELIMITER ;CALL get_salary_stats('Engineering', @avg, @max, @min);
SELECT @avg AS average, @max AS maximum, @min AS minimum;User variables: In MySQL, @variable_name creates a user-defined variable that persists for your session. Perfect for capturing OUT parameter values!
Variables and Declarations
Declare local variables inside procedures to store intermediate values:
DELIMITER //
CREATE PROCEDURE calculate_bonus(IN emp_id INT, OUT bonus DECIMAL(10,2))
BEGIN
DECLARE emp_salary DECIMAL(10,2);
DECLARE years_service INT;
DECLARE performance_rating DECIMAL(3,2);
-- Get employee data
SELECT salary INTO emp_salary
FROM employees
WHERE id = emp_id;
-- Calculate bonus: 10% of salary as base
SET bonus = emp_salary * 0.10;
-- Add performance multiplier (simulated)
SET performance_rating = 1.5;
SET bonus = bonus * performance_rating;
END //
DELIMITER ;CALL calculate_bonus(1, @alice_bonus);
SELECT @alice_bonus AS bonus_amount;Alice earns 14,250.
Variable rules:
- Declare at the beginning of the BEGIN block
- Use DECLARE for local variables
- Use SET or SELECT INTO to assign values
- Variables are case-insensitive
Control Flow - IF, CASE, LOOP
Add logic with conditional statements and loops.
IF-THEN-ELSE:
DELIMITER //
CREATE PROCEDURE categorize_salary(
IN emp_id INT,
OUT category VARCHAR(20)
)
BEGIN
DECLARE emp_salary DECIMAL(10,2);
SELECT salary INTO emp_salary
FROM employees
WHERE id = emp_id;
IF emp_salary >= 90000 THEN
SET category = 'High';
ELSEIF emp_salary >= 70000 THEN
SET category = 'Medium';
ELSE
SET category = 'Low';
END IF;
END //
DELIMITER ;CALL categorize_salary(1, @cat);
SELECT @cat AS salary_category;CASE statement:
DELIMITER //
CREATE PROCEDURE get_dept_budget(IN dept_name VARCHAR(50))
BEGIN
DECLARE budget DECIMAL(12,2);
CASE dept_name
WHEN 'Engineering' THEN SET budget = 500000;
WHEN 'Sales' THEN SET budget = 300000;
WHEN 'Marketing' THEN SET budget = 250000;
ELSE SET budget = 100000;
END CASE;
SELECT dept_name AS department, budget;
END //
DELIMITER ;CALL get_dept_budget('Engineering');WHILE loop:
DELIMITER //
CREATE PROCEDURE generate_numbers(IN max_num INT)
BEGIN
DECLARE counter INT DEFAULT 1;
DROP TEMPORARY TABLE IF EXISTS numbers;
CREATE TEMPORARY TABLE numbers (num INT);
WHILE counter <= max_num DO
INSERT INTO numbers VALUES (counter);
SET counter = counter + 1;
END WHILE;
SELECT * FROM numbers;
END //
DELIMITER ;CALL generate_numbers(5);Loops are uncommon: In practice, set-based operations (SELECT, UPDATE) are usually faster than loops. Use loops only when necessary!
Error Handling with DECLARE HANDLER
Handle errors gracefully with exception handlers:
DELIMITER //
CREATE PROCEDURE safe_insert_employee(
IN emp_name VARCHAR(100),
IN emp_dept VARCHAR(50),
IN emp_salary DECIMAL(10,2),
OUT status VARCHAR(100)
)
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SET status = 'Error: Insert failed';
END;
-- Attempt insert
INSERT INTO employees (name, department, salary)
VALUES (emp_name, emp_dept, emp_salary);
SET status = 'Success: Employee added';
END //
DELIMITER ;-- Success case
CALL safe_insert_employee('Eve', 'HR', 65000, @status);
SELECT @status;Handler types:
- CONTINUE HANDLER: Continue execution after error
- EXIT HANDLER: Exit the procedure after error
- UNDO HANDLER: Rollback and exit (not widely supported)
Common conditions:
- SQLEXCEPTION: Any SQL error
- NOT FOUND: No rows returned (useful with cursors)
- SQLWARNING: SQL warning occurred
- Specific error code: e.g.,
1062for duplicate key
Transactions in Stored Procedures
Stored procedures are perfect for encapsulating transactional logic:
account_id | account_holder | balance |
|---|---|---|
| 1 | Alice | 1000.00 |
| 2 | Bob | 500.00 |
DELIMITER //
CREATE PROCEDURE transfer_money(
IN from_account INT,
IN to_account INT,
IN amount DECIMAL(10,2),
OUT result VARCHAR(100)
)
BEGIN
DECLARE from_balance DECIMAL(10,2);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET result = 'Transfer failed - rolled back';
END;
START TRANSACTION;
-- Check sufficient funds
SELECT balance INTO from_balance
FROM accounts
WHERE account_id = from_account;
IF from_balance < amount THEN
SET result = 'Insufficient funds';
ROLLBACK;
ELSE
-- Deduct from source
UPDATE accounts
SET balance = balance - amount
WHERE account_id = from_account;
-- Add to destination
UPDATE accounts
SET balance = balance + amount
WHERE account_id = to_account;
COMMIT;
SET result = 'Transfer successful';
END IF;
END //
DELIMITER ;-- Transfer $300 from Alice to Bob
CALL transfer_money(1, 2, 300, @result);
SELECT @result;-- Check balances
SELECT * FROM accounts;The procedure ensures atomicity - both updates succeed or both fail!
Stored Functions vs Procedures
Functions are similar to procedures but return a single value and can be used in SELECT statements.
Feature | Stored Procedure | Stored Function |
|---|---|---|
| Returns | Multiple result sets, OUT params | Single value |
| Usage | CALL statement | In SELECT, WHERE, etc. |
| Side effects | Can modify data | Should be read-only |
| Transactions | Can use START TRANSACTION | Limited transaction control |
DELIMITER //
CREATE FUNCTION calculate_tax(amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE tax_rate DECIMAL(4,2) DEFAULT 0.08;
RETURN amount * tax_rate;
END //
DELIMITER ;-- Use function in SELECT
SELECT
product_name,
price,
calculate_tax(price) AS tax,
price + calculate_tax(price) AS total_with_tax
FROM products;When to use functions:
- Return a single calculated value
- Need to use result in SELECT, WHERE, JOIN
- Pure calculation (no data modification)
When to use procedures:
- Perform multiple operations
- Return multiple result sets
- Modify data (INSERT, UPDATE, DELETE)
- Complex business logic with transactions
Common Mistakes to Avoid
Don't make these mistakes:
-
Forgetting DELIMITER: ❌
- MySQL requires DELIMITER to change statement separator
- Without it, procedure creation fails
-
Not handling errors: ❌
- Always use DECLARE HANDLER for critical operations
- Especially important with transactions
-
Using loops instead of set operations: ❌
WHILEloops are slow on large datasets- Use SELECT, UPDATE with WHERE instead
-
Not using transactions for multi-step operations: ❌
- If procedure modifies multiple tables, wrap in transaction
- Ensures atomicity
-
Ignoring parameter types: ❌
- Wrong: accepting INT but passing VARCHAR
- Always match parameter types to expected data
-
Overusing procedures: Sometimes
- Not everything needs a procedure
- Simple queries can stay in application code
-
Not documenting procedures: ❌
- Add comments explaining parameters and logic
- Future you will thank you!
-
SQL injection in dynamic SQL: ❌
- If building queries with CONCAT, sanitize inputs
- Use parameterized queries instead
Key Takeaways
What you learned:
✅ Stored procedures are reusable SQL code saved in the database ✅ CREATE PROCEDURE defines a new procedure with parameters ✅ IN parameters accept input values ✅ OUT parameters return output values ✅ DECLARE creates local variables ✅ IF, CASE, WHILE provide control flow logic ✅ DECLARE HANDLER handles errors gracefully ✅ Transactions in procedures ensure atomicity ✅ Functions return single values, procedures return result sets ✅ GRANT EXECUTE controls who can run procedures ✅ Procedures improve reusability, security, and maintainability
When to use stored procedures:
- Complex business logic used by multiple applications
- Frequently executed queries (performance benefit)
- Operations requiring atomic transactions
- When you need to control data access (security)
- Encapsulating calculations and validations
When NOT to use:
- Simple queries (adds unnecessary complexity)
- Logic that changes frequently (harder to deploy)
- When ORMs provide sufficient abstraction
Coming up next: You'll learn about database security - managing users, roles, and permissions with GRANT and REVOKE!
Practice Exercise: Create these procedures:
get_top_customers(limit INT)- Return top N customers by order totalapply_discount(product_id INT, discount_pct DECIMAL)- Apply discount to productarchive_old_orders(days_old INT)- Move old orders to archive tableget_inventory_value()- Calculate total value of all inventorypromote_employee(emp_id INT, new_salary DECIMAL, OUT status VARCHAR)