This lesson on Stored Procedures is hands-on and example-driven. You will be able to encapsulate, automate, and reuse complex SQL workflows directly inside the database using stored procedures. You will master multi-statement procedure bodies using custom execution delimiters, prevent namespace collisions with parameter naming conventions, and pass dynamic inputs to filter queries at runtime.
What You'll Be Able To Do
- Write and execute stored procedures using CREATE PROCEDURE and CALL statements.
- Redefine client delimiters to bundle multi-statement queries within BEGIN and END blocks.
- Implement DROP PROCEDURE IF EXISTS statements to ensure repeatable script execution.
- Define typed input parameters to dynamically pass arguments into procedure queries.
- Apply standardized variable prefix conventions to prevent ambiguity between parameters and table columns.
Detailed Concept Walkthrough
1. Stored Procedure Definition and Invocation
A stored procedure is a named, precompiled block of SQL statements saved directly in the database catalog for repeatable execution.
- Mechanism: You define a stored procedure with
CREATE PROCEDURE procedure_name()followed by the SQL body, saving the routine permanently in the active schema. - Under the Hood: Stored procedures centralize business logic on the database server, reducing network latency by replacing multiple client-server roundtrips with a single invocation.
- Best Practice: Always execute a
USE database_name;statement before creating or invoking procedures to prevent routines from being assigned to the wrong database schema.
-- Set target schema and create a minimal single-query procedure
USE parks_and_recreation;
CREATE PROCEDURE large_salaries()
SELECT *
FROM employee_salary
WHERE salary >= 50000;
-- Invoke the procedure
CALL large_salaries();
Key Takeaway: Stored procedures store SQL logic on the database server so client applications can execute predefined routines via a single CALL command.
2. Custom Delimiters and Multi-Statement Blocks
Changing the SQL client delimiter allows you to write compound statements containing internal semicolons without terminating the procedure definition prematurely.
- Mechanism: The
DELIMITERcommand reassigns the statement termination character (commonly to$$or//), allowing internal SQL statements within theBEGIN ... ENDblock to use standard semicolons. - Execution Flow: The database parser reads the entire compound block as a single unit until it encounters the custom delimiter at the closing
ENDkeyword. - Best Practice: You must explicitly reset the delimiter back to a semicolon (
DELIMITER ;) immediately after defining the procedure to avoid breaking subsequent queries.
-- Change delimiter to allow internal semicolons inside compound block
DELIMITER $$
CREATE PROCEDURE large_salaries3()
BEGIN
-- First query: high earners
SELECT * FROM employee_salary WHERE salary >= 50000;
-- Second query: base earners
SELECT * FROM employee_salary WHERE salary >= 10000;
END $$
-- Reset delimiter back to standard semicolon
DELIMITER ;
-- Call procedure that returns multiple result sets
CALL large_salaries3();
Key Takeaway: Always switch to a custom delimiter before writing multi-statement BEGIN/END blocks, then reset it back to semicolon immediately after.
3. Safe Scripting and Procedure Management
Defensive procedure scripts use drop-if-exists checks and GUI alteration tools to maintain repeatable schema deployment pipelines.
- Syntax Rule: Executing
CREATE PROCEDUREfor an existing procedure name throws an error unless the prior version is dropped first usingDROP PROCEDURE IF EXISTS procedure_name;. - Under the Hood: Dropping and recreating a procedure replaces its definition in the database metadata table (
information_schema.ROUTINES) without dropping underlying table data. - Best Practice: Wrap stored procedure deployment scripts in idempotent blocks containing schema selection, conditional dropping, delimiter switching, and delimiter resetting.
USE parks_and_recreation;
-- Remove old version to allow clean recreation
DROP PROCEDURE IF EXISTS new_procedure;
DELIMITER $$
CREATE PROCEDURE new_procedure()
BEGIN
SELECT * FROM employee_salary WHERE salary >= 50000;
SELECT * FROM employee_salary WHERE salary >= 10000;
END $$
DELIMITER ;
CALL new_procedure();
Key Takeaway: Prepend procedure definitions with DROP PROCEDURE IF EXISTS to make deployment scripts idempotent and error-free.
4. Dynamic Input Parameters and Naming Conventions
Input parameters allow stored procedures to accept dynamic arguments at runtime and inject them into query filter conditions.
- Mechanism: Parameters are declared within the procedure parentheses by specifying an identifier and a strict SQL data type (e.g.,
p_employee_id INT). - Under the Hood: When
CALL procedure_name(val)is executed, the database binds the provided argument to the parameter variable in the local execution scope of the procedure. - Best Practice: Prefix parameter names with
p_or suffix with_paramto avoid shadowing column names inWHEREclauses (e.g.,WHERE employee_id = p_employee_id).
USE parks_and_recreation;
DROP PROCEDURE IF EXISTS get_employee_salary;
DELIMITER $$
-- Define parameter with explicit data type and prefix convention
CREATE PROCEDURE get_employee_salary(p_employee_id INT)
BEGIN
SELECT salary
FROM employee_salary
WHERE employee_id = p_employee_id;
END $$
DELIMITER ;
-- Invoke procedure with specific employee ID argument
CALL get_employee_salary(1);
Key Takeaway: Prefix parameter names with p_ to prevent naming collisions between procedure variables and table column names.
Topics Covered in Stored Procedures
- Stored Procedure Fundamentals (0:00 - 0:45) — Stored procedures are introduced as reusable database routines that simplify maintenance and optimize query execution.
- Single-Query Procedure Creation (0:45 - 1:55) — A basic stored procedure is created and executed using the CALL statement against the parks_and_recreation database.
- Multi-Query Delimiter Issues (1:55 - 2:40) — Attempting to include multiple statements illustrates why standard semicolons prematurely terminate procedure creation.
- Custom Delimiters and BEGIN-END (2:40 - 3:45) — The delimiter is changed to dollar signs to bundle multiple queries inside a BEGIN and END block before resetting it.
- GUI Tools and Defensive Scripting (3:45 - 4:35) — MySQL Workbench GUI generation demonstrates the automated use of DROP PROCEDURE IF EXISTS and schema qualifiers.
- Parameterized Procedures and Naming (4:35 - 6:00) — Input parameters are added to filter results dynamically, and the p_ prefix convention is applied to prevent column shadowing.
SQL Cheat Sheet
-
CREATE PROCEDURE name() <query>;— Creates a single-query stored procedure in the active schemaCREATE PROCEDURE get_salaries() SELECT * FROM employee_salary; -
CALL procedure_name(args);— Executes a stored procedure with optional argumentsCALL get_salaries(); -
DELIMITER $$— Reassigns the client statement delimiter to custom charactersDELIMITER $$ -
BEGIN ... END— Encloses multiple SQL statements into a single compound blockBEGIN SELECT * FROM employee_salary; END -
DELIMITER ;— Resets the client delimiter back to the standard semicolonDELIMITER ; -
DROP PROCEDURE IF EXISTS name;— Deletes a stored procedure only if it already existsDROP PROCEDURE IF EXISTS get_salaries; -
CREATE PROCEDURE name(p_var TYPE)— Declares a stored procedure accepting typed input parametersCREATE PROCEDURE get_emp(p_id INT) BEGIN SELECT * FROM employee_salary WHERE employee_id = p_id; END
Comparison Table
| Feature | Single-Query Procedure | Compound Parameterized Procedure |
|---|---|---|
| Delimiter Requirement | Standard semicolon (;) is sufficient | Requires temporary custom delimiter ($$) |
| Block Enclosure | No BEGIN ... END required | Mandatory BEGIN ... END block |
| Statement Capacity | Exactly one SQL statement | Multiple SQL statements executed sequentially |
| Input Flexibility | Static execution only | Dynamic runtime arguments accepted |
| Use Case | Simple repeated reads | Complex parameterized data workflows |
Common Pitfalls
- Mistake: Defining a multi-statement BEGIN/END block without changing the DELIMITER. Avoid: Wrap the CREATE PROCEDURE block in DELIMITER $$ and DELIMITER ;.
- Mistake: Forgetting to reset DELIMITER back to semicolon after procedure creation. Avoid: Always place DELIMITER ; immediately following the closing custom delimiter.
- Mistake: Naming an input parameter identically to a table column name. Avoid: Prefix parameters with p_ or suffix with _param to eliminate ambiguous evaluation.
- Mistake: Running CREATE PROCEDURE on a routine name that already exists. Avoid: Add DROP PROCEDURE IF EXISTS procedure_name before the creation script.
FAQs
- Why does MySQL throw a syntax error when creating a procedure with multiple SELECT statements? MySQL treats the first internal semicolon as the end of the CREATE PROCEDURE statement unless you temporarily change the command delimiter with DELIMITER $$.
- What happens if a parameter name matches a column name in the WHERE clause? The SQL parser treats both sides as column references (e.g., WHERE employee_id = employee_id), resulting in an unintended true evaluation for every non-null row.
- Can a stored procedure return more than one output result set? Yes, placing multiple SELECT queries within a BEGIN ... END block produces multiple distinct result tabs or output sets upon CALL execution.
- Are stored procedure parameters strictly typed? Yes, parameters require explicit data types (such as INT, VARCHAR, or DATE) during declaration, and passed arguments must match that type.