🧾 MySQL Stored Procedures – Define, Call, and Manage Reusable SQL Logic

---

🧲 Introduction – Why Use Stored Procedures in MySQL?

Stored procedures are named blocks of SQL logic saved on the MySQL server that can be executed repeatedly using a single CALL statement. They help:

• 🔁 Reduce duplication of code
• 🔒 Centralize logic in the database
• 🚀 Improve performance by reducing client-server communication
• 📦 Package multi-step operations (like validations, inserts, and updates)

🎯 In this guide, you’ll learn:

• What stored procedures are and how to define them
• Syntax for creating, calling, and dropping procedures
• Input/output parameter types
• Best practices and real-world use cases

---

🧱 1. What Is a Stored Procedure?

A Stored Procedure is a set of SQL statements with a name, stored in the database, and executed with a CALL command.

• Supports input (IN), output (OUT), and input/output (INOUT) parameters
• Doesn’t return a value (unlike functions), but can return data via parameters or result sets

---

🛠️ 2. Creating a Stored Procedure

🔹 Basic Syntax

DELIMITER //

CREATE PROCEDURE procedure_name ( [parameters] )
BEGIN
  -- SQL statements
END //

DELIMITER ;

---

🔹 Example – Insert New Customer

DELIMITER //

CREATE PROCEDURE add_customer(
  IN customer_name VARCHAR(100),
  IN customer_email VARCHAR(100)
)
BEGIN
  INSERT INTO customers(name, email)
  VALUES (customer_name, customer_email);
END //

DELIMITER ;

---

📞 3. Calling a Stored Procedure

CALL add_customer('Alice', 'alice@example.com');

✅ Executes the add_customer procedure with provided parameters.

---

🔃 4. IN, OUT, INOUT Parameters

🔹 IN (Input Only)

IN username VARCHAR(50)

✅ Used to pass values into the procedure.

---

🔹 OUT (Output Only)

DELIMITER //

CREATE PROCEDURE get_customer_count(OUT count INT)
BEGIN
  SELECT COUNT(*) INTO count FROM customers;
END //

DELIMITER ;

🔹 Call & Capture Output

CALL get_customer_count(@total);
SELECT @total;

---

🔹 INOUT (Input and Modified Output)

INOUT price DECIMAL(10,2)

✅ Passed in, possibly changed inside the procedure, and returned.

---

🔍 5. Show Stored Procedures

SHOW PROCEDURE STATUS WHERE Db = 'your_database';

---

❌ 6. Drop Stored Procedure

DROP PROCEDURE IF EXISTS add_customer;

---

🔐 7. Grant Execute Privileges

GRANT EXECUTE ON PROCEDURE add_customer TO 'app_user'@'localhost';

✅ Controls who can execute the procedure.

---

🚀 8. Real-World Use Cases

Scenario	Procedure Name	Purpose
Insert a new user	create_user	Accepts name, email, inserts into table
Get monthly sales	sales_summary	Returns aggregated totals for a month
Update order status	update_order_status	Changes status and logs update
Customer info + orders	get_customer_info	Returns customer details and recent orders

---

📘 Best Practices

✅ Tip	💡 Why It Matters
Use DELIMITER // when defining routines	Prevents conflicts with semicolon in body
Keep logic focused	Improves reusability and readability
Use IN, OUT, INOUT correctly	Makes parameters intuitive
Document parameters clearly	Helps future developers understand usage
Avoid business logic that belongs in app	Keep procedures data-centric

---

📌 Summary – Recap & Next Steps

MySQL Stored Procedures allow you to package complex operations and run them efficiently with a single command. They’re perfect for automation, reusability, and centralizing multi-step logic.

🔍 Key Takeaways

• Use CREATE PROCEDURE with IN, OUT, or INOUT parameters
• Call procedures using CALL proc_name(args)
• Store and reuse common database operations
• Use SHOW PROCEDURE STATUS to list available procedures
• Secure execution with GRANT EXECUTE

⚙️ Real-World Relevance

Commonly used in API backends, automation scripts, reporting pipelines, and transactional workflows like e-commerce and CRMs.

---

❓ FAQ – MySQL Stored Procedures

---

❓ What’s the difference between a stored procedure and a stored function?

• Procedure: Executed with CALL, may return result sets
• Function: Returns a single value and can be used in SQL expressions

---

❓ Can I return a result set from a procedure?

✅ Yes. Just use SELECT inside the procedure body.

---

❓ Can a procedure call another procedure?

✅ Yes. You can nest calls between stored routines.

---

❓ How do I change a stored procedure?

You must DROP and then CREATE again—MySQL doesn’t support ALTER PROCEDURE.

---

❓ Can stored procedures use transactions?

✅ Yes. You can use START TRANSACTION, COMMIT, and ROLLBACK within procedures.

---

Share Now :