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.