Creating stored procedures in MySQL - Why You Should Know This

The Big Idea

What if you could save your daily database work with just one simple command?

The Scenario

Imagine you have to run the same set of database commands every day to update sales reports. You write them down and type them manually each time.

The Problem

Typing the same commands over and over is slow and easy to mess up. One small typo can cause errors, and it wastes your time.

The Solution

Stored procedures let you save these commands inside the database once. Then you just call the procedure anytime, quickly and safely.

Before vs After

✗ Before

UPDATE sales SET total = total + 100 WHERE region = 'East';
UPDATE sales SET total = total + 200 WHERE region = 'West';

✓ After

DELIMITER $$
CREATE PROCEDURE UpdateSales()
BEGIN
  UPDATE sales SET total = total + 100 WHERE region = 'East';
  UPDATE sales SET total = total + 200 WHERE region = 'West';
END$$
DELIMITER ;
CALL UpdateSales();

What It Enables

You can automate complex tasks and reuse code easily, saving time and reducing mistakes.

Real Life Example

A store manager runs a stored procedure daily to update inventory counts and sales summaries without typing commands each time.

Key Takeaways

Manual repetition is slow and error-prone.

Stored procedures save commands inside the database.

They make running tasks faster, safer, and reusable.

Practice

1. What is the main purpose of a stored procedure in MySQL?

easy

A. To create a new database

B. To save a set of SQL commands for reuse later

C. To delete tables automatically

D. To backup the entire database

Solution

1. Step 1: Understand stored procedure concept

   A stored procedure is a saved set of SQL commands that can be run multiple times without rewriting.

2. Step 2: Compare options with definition

   Only To save a set of SQL commands for reuse later correctly describes this purpose; others describe unrelated tasks.

3. Final Answer:

   To save a set of SQL commands for reuse later -> Option B

4. Quick Check:

   Stored procedure = saved SQL commands [OK]

Hint: Stored procedures store SQL commands to run later [OK]

Common Mistakes:

• Confusing stored procedures with database creation
• Thinking stored procedures delete tables
• Assuming stored procedures backup data

2. Which of the following is the correct syntax to create a stored procedure named GetUsers without parameters?

easy

A. CREATE PROCEDURE GetUsers() { SELECT * FROM users; }

B. CREATE PROCEDURE GetUsers BEGIN SELECT * FROM users; END;

C. CREATE PROCEDURE GetUsers() SELECT * FROM users;

D. CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM users; END;

Solution

1. Step 1: Recall correct stored procedure syntax

   MySQL requires parentheses for parameters (empty if none), BEGIN and END to mark procedure body.

2. Step 2: Check each option

   CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM users; END; uses parentheses and BEGIN...END correctly. Others miss parentheses, BEGIN/END, or use wrong braces.

3. Final Answer:

   CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM users; END; -> Option D

4. Quick Check:

   Syntax needs () and BEGIN...END [OK]

Hint: Use () even if no parameters, and BEGIN...END block [OK]

Common Mistakes:

• Omitting parentheses after procedure name
• Missing BEGIN and END keywords
• Using curly braces instead of BEGIN...END

3. Given the stored procedure:

CREATE PROCEDURE AddNumbers(IN a INT, IN b INT, OUT sum INT)
BEGIN
  SET sum = a + b;
END;

What will be the value of sum after calling CALL AddNumbers(3, 5, @result); SELECT @result;?

medium

A. 8

B. 35

C. NULL

D. Syntax error

Solution

1. Step 1: Understand procedure parameters and call

   The procedure takes two input integers a and b, and outputs their sum into the OUT parameter sum.

2. Step 2: Analyze the call and output

   Calling with 3 and 5 sets sum = 3 + 5 = 8. The OUT parameter is stored in @result, so SELECT @result returns 8.

3. Final Answer:

   8 -> Option A

4. Quick Check:

   3 + 5 = 8 [OK]

Hint: OUT parameters store results in user variables like @result [OK]

Common Mistakes:

• Confusing concatenation with addition
• Expecting sum to be NULL before assignment
• Forgetting to use user variable for OUT parameter

4. Identify the error in this stored procedure:

CREATE PROCEDURE GetCount()
BEGIN
  SELECT COUNT(*) FROM users
END;

medium

A. Missing semicolon after SELECT statement

B. Procedure name cannot have parentheses

C. BEGIN and END keywords are not allowed

D. COUNT(*) is invalid syntax

Solution

1. Step 1: Check syntax inside procedure body

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

2. Step 2: Identify missing semicolon

   The SELECT COUNT(*) FROM users statement lacks a semicolon before END; causing syntax error.

3. Final Answer:

   Missing semicolon after SELECT statement -> Option A

4. Quick Check:

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

Hint: End each statement with semicolon inside BEGIN...END [OK]

Common Mistakes:

• Omitting semicolon after SQL statements
• Thinking parentheses are not allowed in procedure name
• Believing COUNT(*) is invalid

5. You want to create a stored procedure UpdateSalary that increases an employee's salary by a given percentage. Which of the following is the correct way to write it?

hard

A. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct INT) UPDATE employees SET salary = salary * pct WHERE id = empId;

B. CREATE PROCEDURE UpdateSalary(empId INT, pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId; END;

C. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END;

D. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId END

Solution

1. Step 1: Check parameter declarations and body structure

   CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END; correctly declares input parameters with IN and uses BEGIN...END block.

2. Step 2: Verify salary update logic and syntax

   CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END; updates salary by multiplying with (1 + pct/100), which correctly increases salary by percentage. It also ends statements with semicolon.

3. Step 3: Identify errors in other options

   CREATE PROCEDURE UpdateSalary(empId INT, pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId; END; lacks IN keywords and adds pct directly (not percentage). CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct INT) UPDATE employees SET salary = salary * pct WHERE id = empId; misses BEGIN...END and uses wrong pct type. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId END misses semicolon before END.

4. Final Answer:

   CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END; -> Option C

5. Quick Check:

   Use IN params, BEGIN...END, and correct percentage formula [OK]

Hint: Use IN params and multiply salary by (1 + pct/100) [OK]

Common Mistakes:

• Forgetting IN keyword for parameters
• Adding percentage directly instead of multiplying
• Missing BEGIN...END or semicolons