Before you test stored procedures, you need to understand how they are built. Let us break down the syntax piece by piece. No shortcuts here — you need to read procedure code in production, so you must understand every keyword.
DELIMITER //
CREATE PROCEDURE get_customer_by_id(
IN p_customer_id INT
)
BEGIN
SELECT customer_id, full_name, email, phone, created_at
FROM customers
WHERE customer_id = p_customer_id;
END //
DELIMITER ;
-- Call it
CALL get_customer_by_id(101);DELIMITER // changes the statement terminator from ; to // temporarily. This is needed because the procedure body contains semicolons. After the procedure, we reset it back with DELIMITER ;
Stored procedures have three types of parameters. Think of them like this: IN is what you give the kitchen (your order). OUT is what the kitchen gives back (the dish). INOUT is when you hand something in and get it back modified (like getting your chai topped up).
| Type | Direction | Purpose | Example |
|---|---|---|---|
| IN | Caller -> Procedure | Input values the procedure reads | IN p_account_id VARCHAR(20) |
| OUT | Procedure -> Caller | Output values the procedure sets | OUT p_balance DECIMAL(10,2) |
| INOUT | Both ways | Value goes in, gets modified, comes back | INOUT p_counter INT |
DELIMITER //
-- Procedure with all three parameter types
CREATE PROCEDURE get_account_balance(
IN p_account_id VARCHAR(20), -- Input: which account?
OUT p_balance DECIMAL(10,2), -- Output: current balance
OUT p_status VARCHAR(20) -- Output: account status
)
BEGIN
SELECT balance, status
INTO p_balance, p_status
FROM accounts
WHERE account_id = p_account_id;
END //
DELIMITER ;
-- Calling a procedure with OUT parameters
CALL get_account_balance('ACC001', @bal, @status);
SELECT @bal AS balance, @status AS account_status;
-- Result: balance = 50000.00, account_status = 'ACTIVE'DELIMITER //
CREATE PROCEDURE categorize_customer(
IN p_customer_id INT,
OUT p_category VARCHAR(20)
)
BEGIN
DECLARE v_total_spent DECIMAL(12,2);
-- Calculate total spending
SELECT COALESCE(SUM(amount), 0)
INTO v_total_spent
FROM orders
WHERE customer_id = p_customer_id
AND status = 'COMPLETED';
-- Categorize based on spending
IF v_total_spent >= 100000 THEN
SET p_category = 'PLATINUM';
ELSEIF v_total_spent >= 50000 THEN
SET p_category = 'GOLD';
ELSEIF v_total_spent >= 10000 THEN
SET p_category = 'SILVER';
ELSE
SET p_category = 'REGULAR';
END IF;
END //
DELIMITER ;
-- Test it
CALL categorize_customer(101, @cat);
SELECT @cat; -- Should return the correct tierAlways use COALESCE when using SUM or COUNT in a SELECT INTO. If no rows match, SUM returns NULL — and your variable gets NULL. Then your IF condition fails silently. This is a real production bug I have seen multiple times.
-- List all stored procedures in a database
SHOW PROCEDURE STATUS WHERE Db = 'your_database';
-- See the full code of a procedure
SHOW CREATE PROCEDURE transfer_funds;
-- Drop and recreate (during testing)
DROP PROCEDURE IF EXISTS transfer_funds;
-- Check if a procedure exists
SELECT ROUTINE_NAME, ROUTINE_TYPE, CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_database'
AND ROUTINE_TYPE = 'PROCEDURE';Q: What are IN, OUT, and INOUT parameters in stored procedures?
A: IN parameters are inputs — the caller passes values to the procedure. OUT parameters are outputs — the procedure sets values that the caller reads after execution using session variables (@var). INOUT parameters work both ways — the caller passes a value in, the procedure modifies it, and the caller reads the modified value. For example, in a get_account_balance(IN p_account_id, OUT p_balance) procedure, account_id goes in and balance comes out.
Key Point: Stored procedures use IN parameters for input, OUT parameters for output, and INOUT for both. Always use DELIMITER when creating procedures in MySQL.