Creating stored procedures in MySQL - Try It Yourself

Creating Stored Procedures in MySQL

📖 Scenario: You work at a small bookstore that keeps track of books and sales in a MySQL database. To make common tasks easier, you want to create stored procedures that can be reused without writing the same SQL again and again.

🎯 Goal: Build a stored procedure in MySQL that inserts a new book into the books table, and another stored procedure that retrieves books by a minimum price.

📋 What You'll Learn

Create a books table with columns id (auto-increment primary key), title (varchar), and price (decimal).

Create a stored procedure called add_book that inserts a new book with title and price.

Create a stored procedure called get_books_by_min_price that returns books with price greater than or equal to a given value.

Use proper MySQL syntax for stored procedures including delimiters.

💡 Why This Matters

🌍 Real World

Stored procedures help automate repetitive database tasks like inserting or querying data, making applications more efficient and easier to maintain.

💼 Career

Knowing how to create and use stored procedures is a valuable skill for database administrators and backend developers working with MySQL or similar databases.

1

Create the books table

Write SQL code to create a table called books with these columns: id as an auto-increment primary key, title as VARCHAR(100), and price as DECIMAL(5,2).

MySQL

-- Your code here to create the books table

Hint

Use CREATE TABLE with the specified columns and types. Remember to set id as primary key and auto-increment.

2

Set the delimiter for stored procedure creation

Write SQL code to change the delimiter to $$ so you can define stored procedures without conflicts.

MySQL

CREATE TABLE books (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

-- Change delimiter to $$ below

Hint

Use DELIMITER $$ to change the statement delimiter before creating stored procedures.

3

Create the add_book stored procedure

Write a stored procedure called add_book that takes two input parameters: book_title (VARCHAR(100)) and book_price (DECIMAL(5,2)). The procedure should insert a new row into the books table using these parameters. End the procedure with DELIMITER ;.

MySQL

CREATE TABLE books (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

DELIMITER $$

-- Create add_book procedure below

Hint

Define the procedure with CREATE PROCEDURE, specify input parameters with IN, and use INSERT INTO inside BEGIN ... END. Reset delimiter to ; after.

4

Create the get_books_by_min_price stored procedure

Write a stored procedure called get_books_by_min_price that takes one input parameter min_price (DECIMAL(5,2)) and returns all rows from books where price is greater than or equal to min_price. Use SELECT * FROM books WHERE price >= min_price; inside the procedure. End the procedure with DELIMITER ;.

MySQL

CREATE TABLE books (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

DELIMITER $$

CREATE PROCEDURE add_book(IN book_title VARCHAR(100), IN book_price DECIMAL(5,2))
BEGIN
  INSERT INTO books (title, price) VALUES (book_title, book_price);
END$$

-- Create get_books_by_min_price procedure below

Hint

Define the procedure with one input parameter, and use a SELECT statement with a WHERE clause inside BEGIN ... END. Reset delimiter to ; after.

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