Creating stored procedures in MySQL - Visual Walkthrough

Concept Flow - Creating stored procedures

Start

↓

Define Procedure Name

↓

Specify Parameters (IN/OUT)

↓

Write SQL Statements

↓

End Procedure Definition

↓

Call Procedure to Execute

↓

Finish

This flow shows how to create a stored procedure by defining its name, parameters, SQL code, ending the definition, and then calling it to run.

Execution Sample

MySQL

DELIMITER $$
CREATE PROCEDURE GetAllUsers()
BEGIN
  SELECT * FROM users;
END$$
DELIMITER ;

This code creates a stored procedure named GetAllUsers that selects all rows from the users table.

Execution Table

Step	Action	Evaluation	Result
1	Set DELIMITER to $$	DELIMITER changed	Allows procedure body to contain ; without ending statement
2	CREATE PROCEDURE GetAllUsers()	Procedure signature accepted	Procedure named GetAllUsers created
3	BEGIN	Start of procedure body	Ready to write SQL statements
4	SELECT * FROM users;	Query inside procedure	Selects all rows from users table
5	END$$	End of procedure body	Procedure definition completed
6	DELIMITER ;	Reset DELIMITER to ;	Normal statement delimiter restored
7	CALL GetAllUsers();	Execute procedure	Returns all rows from users table
8	Procedure execution ends	No more steps	Finished

💡 Procedure created and executed successfully; no more steps.

Variable Tracker

Variable	Start	After Step 2	After Step 5	After Step 7	Final
DELIMITER	;	$$	$$	;	;
Procedure GetAllUsers	Not defined	Defined	Defined	Defined	Defined
Execution Result	None	None	None	All rows from users table	All rows from users table

Key Moments - 3 Insights

Why do we change the DELIMITER before creating the procedure?

We change DELIMITER to $$ (see Step 1) so that the semicolons inside the procedure body don't end the CREATE PROCEDURE statement prematurely.

What happens if we forget to reset DELIMITER back to ; after defining the procedure?

If DELIMITER is not reset (Step 6), normal SQL statements won't end correctly, causing errors when running other commands.

How do we run the stored procedure after creating it?

We use the CALL statement (Step 7) with the procedure name and parentheses to execute it and get the results.

Visual Quiz - 3 Questions

Look at the execution table, what is the DELIMITER value after Step 5?

A /

B ;

C $$

D :

💡 Hint

Check the 'DELIMITER' row in variable_tracker after Step 5.

At which step is the stored procedure actually executed?

A Step 3

B Step 7

C Step 5

D Step 2

💡 Hint

Look for the CALL statement in the execution_table.

If we omit the BEGIN and END keywords, what will happen?

A Syntax error during creation

B Procedure will still work fine

C Procedure will execute but return no results

D Procedure will execute twice

💡 Hint

Refer to Step 3 and Step 5 where BEGIN and END mark the procedure body.

Concept Snapshot

CREATE PROCEDURE proc_name([parameters])
BEGIN
  SQL statements;
END;
Use DELIMITER to change statement end marker when defining.
Call procedure with CALL proc_name();
Stored procedures group SQL for reuse.

Full Transcript

Creating stored procedures in MySQL involves changing the DELIMITER to avoid conflicts with semicolons inside the procedure, defining the procedure with a name and optional parameters, writing SQL statements inside BEGIN and END blocks, then resetting the DELIMITER. After creation, the procedure is executed using the CALL statement. This process helps organize and reuse SQL code efficiently.