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

Need a 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

Need a 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

Need a 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

Need a hint?

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