Why stored procedures are needed in SQL - Visual Breakdown

Concept Flow - Why stored procedures are needed

Write SQL commands

↓

Create Stored Procedure

↓

Store in Database

↓

Call Procedure

↓

Execute commands inside

↓

Return results or perform actions

Stored procedures are written once, saved in the database, and called repeatedly to run multiple SQL commands efficiently.

Execution Sample

SQL

CREATE PROCEDURE GetUserById(IN userId INT)
BEGIN
  SELECT * FROM Users WHERE Id = userId;
END;

CALL GetUserById(3);

This stored procedure fetches user details by user ID when called.

Execution Table

Step	Action	Input	Process	Output
1	Create Procedure	GetUserById(userId)	Store SQL SELECT in DB	Procedure saved
2	Call Procedure	userId = 3	Run SELECT * FROM Users WHERE Id = userId	User data with Id=3
3	Return Result	User data	Send data to caller	User row displayed
4	End	-	-	Procedure execution complete

💡 Procedure ends after returning the requested user data.

Variable Tracker

Variable	Start	After Call	Final
userId	undefined	3	3
Procedure State	Not created	Created	Executed and finished

Key Moments - 2 Insights

Why do we create a stored procedure instead of running the SELECT query directly?

Stored procedures save the SQL commands in the database for reuse, reducing repeated typing and improving performance as shown in execution_table step 1 and 2.

What happens when we call the stored procedure with a specific userId?

The procedure runs the saved SELECT query using the input userId, fetching the matching user data as seen in execution_table step 2.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output after calling the procedure with userId=3?

A Procedure saved

B User data with Id=3

C No data found

D Error message

💡 Hint

Check execution_table row 2 under Output column.

At which step does the procedure get stored in the database?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at execution_table row 1 under Process column.

If we call the procedure with userId=5 instead of 3, what changes in variable_tracker?

A userId changes to 5 after call

B Procedure State changes to 'Not created'

C userId remains undefined

D Procedure State resets to 'Executed and finished'

💡 Hint

See variable_tracker row for userId after call.

Concept Snapshot

Stored procedures are saved SQL commands in the database.
They run multiple commands with one call.
Input parameters let you customize execution.
They improve performance and reduce errors.
Call them with CALL procedureName(params);

Full Transcript

Stored procedures are like saved sets of instructions in a database. Instead of writing the same SQL commands again and again, you write them once inside a procedure and save it. When you want to run those commands, you just call the procedure with any needed inputs. The database then runs the stored commands and returns the results. This saves time, reduces mistakes, and can make your database faster. For example, a procedure to get user details by ID stores the SELECT query. When called with a user ID, it returns that user's data quickly. This step-by-step flow shows how the procedure is created, stored, called, and executed.

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