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

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

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

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

Hint

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

Practice

1. Why are stored procedures useful in a database system?

easy

A. They slow down the database to prevent overload.

B. They make the database look colorful and pretty.

C. They allow users to change data without any rules.

D. They save time by running pre-written commands quickly.

Solution

1. Step 1: Understand stored procedures purpose

   Stored procedures are saved sets of commands that run quickly when called.

2. Step 2: Identify the benefit in options

   They save time by running pre-written commands quickly. says they save time by running pre-written commands, which matches the purpose.

3. Final Answer:

   They save time by running pre-written commands quickly. -> Option D

4. Quick Check:

   Stored procedures save time = A [OK]

Hint: Stored procedures run saved commands fast [OK]

Common Mistakes:

• Thinking stored procedures change database appearance
• Believing stored procedures allow rule-free data changes
• Assuming stored procedures slow down the system

2. Which of the following is the correct way to call a stored procedure named GetUserData in SQL?

easy

A. CALL GetUserData();

B. EXECUTE GetUserData;

C. RUN PROCEDURE GetUserData();

D. START GetUserData();

Solution

1. Step 1: Recall stored procedure call syntax

   Standard SQL uses the CALL keyword followed by procedure name and parentheses.

2. Step 2: Match syntax with options

   CALL GetUserData(); uses CALL GetUserData(); which is correct syntax.

3. Final Answer:

   CALL GetUserData(); -> Option A

4. Quick Check:

   CALL syntax = D [OK]

Hint: Use CALL to run stored procedures [OK]

Common Mistakes:

• Using EXECUTE without parentheses
• Using RUN PROCEDURE which is invalid
• Using START which is not SQL syntax

3. Consider this stored procedure that returns the count of users older than 30:

CREATE PROCEDURE CountOlderUsers()
BEGIN
  SELECT COUNT(*) AS UserCount FROM Users WHERE Age > 30;
END;

What will be the output when you run CALL CountOlderUsers(); if the Users table has 5 users older than 30?

medium

A. UserCount = 5

B. UserCount = 0

C. Syntax error

D. No output

Solution

1. Step 1: Understand the procedure logic

   The procedure counts users with Age > 30 and returns that count as UserCount.

2. Step 2: Apply given data

   Since 5 users are older than 30, the count returned will be 5.

3. Final Answer:

   UserCount = 5 -> Option A

4. Quick Check:

   Count of users older than 30 = 5 [OK]

Hint: Count matches condition in SELECT query [OK]

Common Mistakes:

• Assuming no output because procedure has no RETURN
• Thinking syntax error due to missing parameters
• Confusing count with total users

4. You wrote this stored procedure:

CREATE PROCEDURE UpdateSalary()
BEGIN
  UPDATE Employees SET Salary = Salary + 1000
END;

When you try to run CALL UpdateSalary();, you get an error. What is the likely problem?

medium

A. Procedure name cannot have uppercase letters

B. Missing semicolon after UPDATE statement

C. CALL keyword is incorrect

D. Salary column cannot be updated

Solution

1. Step 1: Check procedure syntax

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

2. Step 2: Identify missing semicolon

   The UPDATE statement lacks a semicolon at the end, causing syntax error.

3. Final Answer:

   Missing semicolon after UPDATE statement -> Option B

4. Quick Check:

   Semicolon missing = B [OK]

Hint: End each SQL statement with semicolon inside procedures [OK]

Common Mistakes:

• Thinking procedure names can't have uppercase letters
• Believing CALL keyword is wrong
• Assuming Salary column is read-only

5. A company wants to ensure that every time a new order is added, the total sales are updated automatically. Which benefit of stored procedures best explains why using a stored procedure for this task is a good idea?

hard

A. Stored procedures allow users to edit data without restrictions.

B. Stored procedures slow down the database to avoid errors.

C. Stored procedures help keep data safe and organized by controlling updates.

D. Stored procedures make the database design more colorful.

Solution

1. Step 1: Understand the task requirement

   The company wants automatic, controlled updates to total sales when orders are added.

2. Step 2: Match benefit of stored procedures

   Stored procedures help keep data safe and organized by controlling how updates happen, which fits the need.

3. Final Answer:

   Stored procedures help keep data safe and organized by controlling updates. -> Option C

4. Quick Check:

   Data safety and control = C [OK]

Hint: Stored procedures control and protect data updates [OK]

Common Mistakes:

• Thinking stored procedures allow unrestricted data edits
• Believing stored procedures slow down the system
• Confusing appearance with functionality