IF-ELSE in procedures in SQL - Mini Project: Build & Apply

Using IF-ELSE in SQL Procedures

📖 Scenario: You work at a bookstore that wants to categorize books by their price range automatically.They want a procedure that takes a book's price and returns a category: 'Cheap', 'Moderate', or 'Expensive'.

🎯 Goal: Create a SQL procedure named categorize_book_price that uses IF-ELSE statements to assign a category based on the price input.

📋 What You'll Learn

Create a procedure named categorize_book_price with an input parameter book_price of type DECIMAL.

Declare a variable price_category to hold the category as VARCHAR(20).

Use IF-ELSE statements to set price_category to 'Cheap' if book_price is less than 10.

Set price_category to 'Moderate' if book_price is between 10 and 20 inclusive.

Set price_category to 'Expensive' if book_price is greater than 20.

Return the price_category as an output parameter.

💡 Why This Matters

🌍 Real World

Bookstores and retail systems often need to categorize products automatically based on price or other attributes to help with pricing strategies and customer information.

💼 Career

Knowing how to write conditional logic in SQL procedures is essential for database developers and analysts to automate data processing and business rules.

1

Create the procedure header and input parameter

Write the first line to create a procedure named categorize_book_price with one input parameter called book_price of type DECIMAL(5,2).

SQL

-- Create procedure header with input parameter book_price
-- Your code here

Need a hint?

Start with CREATE PROCEDURE and define book_price as an input parameter.

2

Declare a variable to hold the price category

Inside the procedure, declare a variable named price_category of type VARCHAR(20) to store the category.

SQL

CREATE PROCEDURE categorize_book_price(IN book_price DECIMAL(5,2))
BEGIN
    -- Declare price_category variable
    -- Your code here
END;

Need a hint?

Use DECLARE inside the procedure body to create the variable.

3

Add IF-ELSE logic to set the price category

Write IF-ELSE statements inside the procedure to set price_category to 'Cheap' if book_price < 10, 'Moderate' if between 10 and 20 inclusive, and 'Expensive' if > 20.

SQL

CREATE PROCEDURE categorize_book_price(IN book_price DECIMAL(5,2))
BEGIN
    DECLARE price_category VARCHAR(20);
    -- Add IF-ELSE logic here
    -- Your code here
END;

Need a hint?

Use IF ... THEN ... ELSEIF ... ELSE ... END IF; structure and SET to assign the variable.

4

Add an output parameter to return the category

Modify the procedure to add an output parameter named category of type VARCHAR(20), and set it to the value of price_category before ending the procedure.

SQL

CREATE PROCEDURE categorize_book_price(IN book_price DECIMAL(5,2), OUT category VARCHAR(20))
BEGIN
    DECLARE price_category VARCHAR(20);
    IF book_price < 10 THEN
        SET price_category = 'Cheap';
    ELSEIF book_price <= 20 THEN
        SET price_category = 'Moderate';
    ELSE
        SET price_category = 'Expensive';
    END IF;
    -- Set output parameter category
    -- Your code here
END;

Need a hint?

Add OUT category VARCHAR(20) to the procedure parameters and assign it inside the procedure.