Why stored procedures are needed in SQL - See It in Action

Understanding Why Stored Procedures Are Needed

📖 Scenario: You are working as a database assistant for a small online bookstore. The store needs to calculate the total sales for a given day repeatedly. Instead of writing the same SQL query every time, you will create a stored procedure to make this task easier and faster.

🎯 Goal: Build a stored procedure that calculates total sales for a specific date. This will help the bookstore staff get sales data quickly without rewriting queries.

📋 What You'll Learn

Create a table called sales with columns sale_id, sale_date, and amount.

Insert sample sales data into the sales table.

Create a stored procedure called GetTotalSales that takes a date parameter and returns the total sales amount for that date.

Call the stored procedure with a specific date to get the total sales.

💡 Why This Matters

🌍 Real World

Stored procedures help businesses run common database tasks quickly and securely without rewriting queries every time.

💼 Career

Knowing stored procedures is important for database administrators and developers to optimize database operations and improve performance.

1

Create the sales table and insert sample data

Write SQL code to create a table called sales with columns sale_id (integer), sale_date (date), and amount (decimal). Then insert these exact rows: (1, '2024-06-01', 100.50), (2, '2024-06-01', 200.75), (3, '2024-06-02', 150.00).

SQL

-- Create the sales table and insert sample data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Declare the stored procedure header

Write SQL code to start creating a stored procedure named GetTotalSales that takes one input parameter called input_date of type DATE. Use the syntax: CREATE PROCEDURE GetTotalSales (IN input_date DATE).

SQL

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_id, sale_date, amount) VALUES
(1, '2024-06-01', 100.50),
(2, '2024-06-01', 200.75),
(3, '2024-06-02', 150.00);

-- Start creating the stored procedure GetTotalSales with input parameter input_date
-- Your code here

Need a hint?

Use CREATE PROCEDURE with IN parameter and open the procedure body with BEGIN.

3

Write the query inside the stored procedure

Inside the stored procedure body, write a SQL query that selects the sum of amount from the sales table where sale_date equals the input parameter input_date. Use SELECT SUM(amount) FROM sales WHERE sale_date = input_date;.

SQL

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_id, sale_date, amount) VALUES
(1, '2024-06-01', 100.50),
(2, '2024-06-01', 200.75),
(3, '2024-06-02', 150.00);

CREATE PROCEDURE GetTotalSales (IN input_date DATE)
BEGIN
-- Write the query to sum amount for the input_date
-- Your code here

Need a hint?

Use SELECT SUM(amount) FROM sales WHERE sale_date = input_date; inside the procedure.

4

Complete the stored procedure and call it

Close the stored procedure with END;. Then write a SQL statement to call the stored procedure with the date '2024-06-01' using CALL GetTotalSales('2024-06-01');.

SQL

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_id, sale_date, amount) VALUES
(1, '2024-06-01', 100.50),
(2, '2024-06-01', 200.75),
(3, '2024-06-02', 150.00);

CREATE PROCEDURE GetTotalSales (IN input_date DATE)
BEGIN
    SELECT SUM(amount) FROM sales WHERE sale_date = input_date;
END;

-- Call the stored procedure for date '2024-06-01'
-- Your code here

Need a hint?

Close the procedure with END; and use CALL GetTotalSales('2024-06-01'); to run it.