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.

Practice

1. What is the main purpose of a stored procedure in MySQL?

easy

A. To create a new database

B. To save a set of SQL commands for reuse later

C. To delete tables automatically

D. To backup the entire database

Solution

1. Step 1: Understand stored procedure concept

   A stored procedure is a saved set of SQL commands that can be run multiple times without rewriting.

2. Step 2: Compare options with definition

   Only To save a set of SQL commands for reuse later correctly describes this purpose; others describe unrelated tasks.

3. Final Answer:

   To save a set of SQL commands for reuse later -> Option B

4. Quick Check:

   Stored procedure = saved SQL commands [OK]

Hint: Stored procedures store SQL commands to run later [OK]

Common Mistakes:

• Confusing stored procedures with database creation
• Thinking stored procedures delete tables
• Assuming stored procedures backup data

2. Which of the following is the correct syntax to create a stored procedure named GetUsers without parameters?

easy

A. CREATE PROCEDURE GetUsers() { SELECT * FROM users; }

B. CREATE PROCEDURE GetUsers BEGIN SELECT * FROM users; END;

C. CREATE PROCEDURE GetUsers() SELECT * FROM users;

D. CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM users; END;

Solution

1. Step 1: Recall correct stored procedure syntax

   MySQL requires parentheses for parameters (empty if none), BEGIN and END to mark procedure body.

2. Step 2: Check each option

   CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM users; END; uses parentheses and BEGIN...END correctly. Others miss parentheses, BEGIN/END, or use wrong braces.

3. Final Answer:

   CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM users; END; -> Option D

4. Quick Check:

   Syntax needs () and BEGIN...END [OK]

Hint: Use () even if no parameters, and BEGIN...END block [OK]

Common Mistakes:

• Omitting parentheses after procedure name
• Missing BEGIN and END keywords
• Using curly braces instead of BEGIN...END

3. Given the stored procedure:

CREATE PROCEDURE AddNumbers(IN a INT, IN b INT, OUT sum INT)
BEGIN
  SET sum = a + b;
END;

What will be the value of sum after calling CALL AddNumbers(3, 5, @result); SELECT @result;?

medium

A. 8

B. 35

C. NULL

D. Syntax error

Solution

1. Step 1: Understand procedure parameters and call

   The procedure takes two input integers a and b, and outputs their sum into the OUT parameter sum.

2. Step 2: Analyze the call and output

   Calling with 3 and 5 sets sum = 3 + 5 = 8. The OUT parameter is stored in @result, so SELECT @result returns 8.

3. Final Answer:

   8 -> Option A

4. Quick Check:

   3 + 5 = 8 [OK]

Hint: OUT parameters store results in user variables like @result [OK]

Common Mistakes:

• Confusing concatenation with addition
• Expecting sum to be NULL before assignment
• Forgetting to use user variable for OUT parameter

4. Identify the error in this stored procedure:

CREATE PROCEDURE GetCount()
BEGIN
  SELECT COUNT(*) FROM users
END;

medium

A. Missing semicolon after SELECT statement

B. Procedure name cannot have parentheses

C. BEGIN and END keywords are not allowed

D. COUNT(*) is invalid syntax

Solution

1. Step 1: Check syntax inside procedure body

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

2. Step 2: Identify missing semicolon

   The SELECT COUNT(*) FROM users statement lacks a semicolon before END; causing syntax error.

3. Final Answer:

   Missing semicolon after SELECT statement -> Option A

4. Quick Check:

   Statements inside BEGIN...END need semicolons [OK]

Hint: End each statement with semicolon inside BEGIN...END [OK]

Common Mistakes:

• Omitting semicolon after SQL statements
• Thinking parentheses are not allowed in procedure name
• Believing COUNT(*) is invalid

5. You want to create a stored procedure UpdateSalary that increases an employee's salary by a given percentage. Which of the following is the correct way to write it?

hard

A. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct INT) UPDATE employees SET salary = salary * pct WHERE id = empId;

B. CREATE PROCEDURE UpdateSalary(empId INT, pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId; END;

C. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END;

D. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId END

Solution

1. Step 1: Check parameter declarations and body structure

   CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END; correctly declares input parameters with IN and uses BEGIN...END block.

2. Step 2: Verify salary update logic and syntax

   CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END; updates salary by multiplying with (1 + pct/100), which correctly increases salary by percentage. It also ends statements with semicolon.

3. Step 3: Identify errors in other options

   CREATE PROCEDURE UpdateSalary(empId INT, pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId; END; lacks IN keywords and adds pct directly (not percentage). CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct INT) UPDATE employees SET salary = salary * pct WHERE id = empId; misses BEGIN...END and uses wrong pct type. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId END misses semicolon before END.

4. Final Answer:

   CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END; -> Option C

5. Quick Check:

   Use IN params, BEGIN...END, and correct percentage formula [OK]

Hint: Use IN params and multiply salary by (1 + pct/100) [OK]

Common Mistakes:

• Forgetting IN keyword for parameters
• Adding percentage directly instead of multiplying
• Missing BEGIN...END or semicolons