Why stored procedures centralize logic in MySQL - See It in Action

Why Stored Procedures Centralize Logic

📖 Scenario: You work for a company that manages customer orders. The company wants to ensure that the logic for adding new orders is consistent and secure. Instead of writing the same SQL commands in many places, they want to use a stored procedure to centralize the logic.

🎯 Goal: Build a stored procedure in MySQL that inserts a new order into an orders table. This procedure will centralize the logic for adding orders, making it easier to maintain and secure.

📋 What You'll Learn

Create an orders table with columns order_id (auto-increment primary key), customer_name (VARCHAR), and order_amount (DECIMAL).

Create a stored procedure called add_order that takes customer_name and order_amount as input parameters.

The stored procedure should insert a new row into the orders table using the input parameters.

Demonstrate calling the stored procedure to add a new order.

💡 Why This Matters

🌍 Real World

Companies use stored procedures to keep important database operations consistent and secure, avoiding repeated code in many places.

💼 Career

Database developers and administrators often write stored procedures to centralize business logic, improving maintainability and security.

1

Create the orders table

Write a SQL statement to create a table called orders with these columns: order_id as an auto-increment primary key, customer_name as VARCHAR(100), and order_amount as DECIMAL(10,2).

MySQL

-- Your code here to create the orders table

Hint

Use CREATE TABLE with AUTO_INCREMENT for the primary key.

2

Create the stored procedure add_order

Write a SQL statement to create a stored procedure called add_order that takes two input parameters: in_customer_name (VARCHAR(100)) and in_order_amount (DECIMAL(10,2)). The procedure should insert a new row into the orders table using these parameters.

MySQL

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(100),
  order_amount DECIMAL(10,2)
);
-- Your code here to create the stored procedure add_order

Hint

Use CREATE PROCEDURE with IN parameters and an INSERT statement inside BEGIN ... END.

3

Call the stored procedure to add an order

Write a SQL statement to call the stored procedure add_order with 'Alice' as customer_name and 150.75 as order_amount.

MySQL

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(100),
  order_amount DECIMAL(10,2)
);

DELIMITER $$
CREATE PROCEDURE add_order(
  IN in_customer_name VARCHAR(100),
  IN in_order_amount DECIMAL(10,2)
)
BEGIN
  INSERT INTO orders (customer_name, order_amount) VALUES (in_customer_name, in_order_amount);
END$$
DELIMITER ;

-- Your code here to call the stored procedure add_order

Hint

Use the CALL statement with the procedure name and parameters.

4

Explain why stored procedures centralize logic

Add a SQL comment explaining why using the stored procedure add_order centralizes the logic for adding orders in the database.

MySQL

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(100),
  order_amount DECIMAL(10,2)
);

DELIMITER $$
CREATE PROCEDURE add_order(
  IN in_customer_name VARCHAR(100),
  IN in_order_amount DECIMAL(10,2)
)
BEGIN
  INSERT INTO orders (customer_name, order_amount) VALUES (in_customer_name, in_order_amount);
END$$
DELIMITER ;

CALL add_order('Alice', 150.75);

-- Your comment here explaining centralization of logic

Hint

Write a comment starting with -- explaining that the procedure keeps the insert logic in one place.

Practice

1. Why do stored procedures help centralize logic in a MySQL database?

easy

A. Because they speed up the network connection

B. Because they automatically create database backups

C. Because they replace the need for tables

D. Because they keep all related SQL code in one place for reuse and maintenance

Solution

1. Step 1: Understand the role of stored procedures

   Stored procedures contain SQL code that can be executed repeatedly without rewriting it.

2. Step 2: Recognize centralization benefits

   By placing logic inside stored procedures, all applications use the same code, making maintenance easier and consistent.

3. Final Answer:

   Because they keep all related SQL code in one place for reuse and maintenance -> Option D

4. Quick Check:

   Centralizing logic = reuse and maintenance [OK]

Hint: Stored procedures group SQL logic centrally for easy reuse [OK]

Common Mistakes:

• Thinking stored procedures create backups
• Confusing stored procedures with tables
• Assuming stored procedures affect network speed

2. Which of the following is the correct syntax to create a stored procedure named GetUser in MySQL?

easy

A. CREATE PROCEDURE GetUser BEGIN SELECT * FROM users END

B. CREATE PROCEDURE GetUser() BEGIN SELECT * FROM users; END;

C. CREATE FUNCTION GetUser() SELECT * FROM users;

D. CREATE PROCEDURE GetUser() { SELECT * FROM users; }

Solution

1. Step 1: Recall MySQL stored procedure syntax

   Stored procedures use CREATE PROCEDURE name() BEGIN ... END; with semicolons inside.

2. Step 2: Check each option

   CREATE PROCEDURE GetUser() BEGIN SELECT * FROM users; END; uses correct syntax with BEGIN and END and semicolons; others miss BEGIN/END or use wrong keywords or braces.

3. Final Answer:

   CREATE PROCEDURE GetUser() BEGIN SELECT * FROM users; END; -> Option B

4. Quick Check:

   Correct syntax includes BEGIN...END with semicolons [OK]

Hint: Stored procedures need BEGIN and END blocks with semicolons [OK]

Common Mistakes:

• Using CREATE FUNCTION instead of PROCEDURE
• Omitting BEGIN and END
• Using curly braces instead of BEGIN/END

3. Given the stored procedure:

CREATE PROCEDURE GetCount()
BEGIN
  SELECT COUNT(*) AS total FROM orders;
END;

What will be the output when calling CALL GetCount(); if the orders table has 5 rows?

medium

A. A result set with one row and column 'total' showing 5

B. An error because no parameters are passed

C. No output because SELECT inside procedure does not return data

D. A result set with 5 rows showing order details

Solution

1. Step 1: Understand what the procedure does

   The procedure runs SELECT COUNT(*) from orders, which returns one row with the count.

2. Step 2: Predict output of CALL

   Calling the procedure returns the SELECT result as a result set with one row and column named 'total' showing 5.

3. Final Answer:

   A result set with one row and column 'total' showing 5 -> Option A

4. Quick Check:

   COUNT(*) returns number of rows = 5 [OK]

Hint: CALL runs procedure and returns SELECT results as output [OK]

Common Mistakes:

• Thinking SELECT inside procedure does not return data
• Confusing COUNT(*) with row details
• Expecting error due to no parameters

4. Consider this stored procedure code:

CREATE PROCEDURE UpdateStock()
BEGIN
  UPDATE products SET stock = stock - 1 WHERE id = 10
END;

What is the error in this code?

medium

A. Using UPDATE inside a procedure is not allowed

B. Incorrect procedure name syntax

C. Missing semicolon after UPDATE statement

D. Missing parentheses after procedure name

Solution

1. Step 1: Check syntax inside BEGIN...END

   Each statement inside BEGIN...END must end with a semicolon in MySQL.

2. Step 2: Identify missing semicolon

   The UPDATE line lacks a semicolon at the end, causing a syntax error.

3. Final Answer:

   Missing semicolon after UPDATE statement -> Option C

4. Quick Check:

   Statements inside BEGIN...END need semicolons [OK]

Hint: Always end statements inside procedures with semicolons [OK]

Common Mistakes:

• Forgetting semicolon after SQL statements
• Thinking procedure name syntax is wrong
• Believing UPDATE is disallowed in procedures

5. A company wants to ensure all applications update customer balances the same way. Which benefit of stored procedures best supports this goal?

hard

A. Stored procedures centralize logic to ensure consistent data handling

B. Stored procedures improve network speed for updates

C. Stored procedures automatically create audit logs

D. Stored procedures allow direct table access without permissions

Solution

1. Step 1: Understand the goal

   The company wants consistent updates to customer balances across all applications.

2. Step 2: Identify stored procedure benefit

   Centralizing update logic in stored procedures ensures all apps use the same code, preventing inconsistencies.

3. Final Answer:

   Stored procedures centralize logic to ensure consistent data handling -> Option A

4. Quick Check:

   Centralized logic = consistent data updates [OK]

Hint: Centralize update logic to keep data consistent across apps [OK]

Common Mistakes:

• Assuming stored procedures speed up network
• Thinking they auto-create audit logs
• Believing stored procedures bypass permissions