Creating stored procedures in MySQL - Mechanics & Internals

Overview - Creating stored procedures

What is it?

A stored procedure is a saved set of SQL commands that you can run repeatedly in a database. It works like a recipe that the database remembers and can follow whenever you ask. Instead of writing the same SQL commands again and again, you just call the stored procedure. This helps keep your database work organized and efficient.

Why it matters

Stored procedures save time and reduce mistakes by reusing SQL code easily. Without them, you would have to write the same commands repeatedly, which can lead to errors and slow work. They also help keep data safe by controlling how users interact with the database. This makes managing data faster, safer, and more reliable.

Where it fits

Before learning stored procedures, you should understand basic SQL commands like SELECT, INSERT, UPDATE, and DELETE. After mastering stored procedures, you can learn about triggers, functions, and advanced database programming to automate and optimize your data tasks.

Mental Model

Core Idea

A stored procedure is like a saved recipe of SQL steps that the database can follow anytime you ask.

Think of it like...

Imagine a cookbook where you write down your favorite recipes. Instead of remembering all the steps every time you cook, you just open the book and follow the recipe. Stored procedures work the same way for databases.

┌─────────────────────────────┐
│       Stored Procedure      │
│ ┌─────────────────────────┐ │
│ │ SQL Commands (Recipe)   │ │
│ └─────────────────────────┘ │
└──────────────┬──────────────┘
               │
       Call the procedure
               │
       ┌───────▼────────┐
       │ Database runs   │
       │ the SQL steps   │
       └─────────────────┘

Build-Up - 7 Steps

1

Foundation

What is a stored procedure

Concept: Introduces the idea of storing SQL commands as reusable blocks.

A stored procedure is a named group of SQL statements saved in the database. You create it once and then run it many times by calling its name. This saves you from typing the same commands repeatedly.

Result

You have a saved SQL routine that you can run anytime by calling its name.

Understanding that stored procedures are saved commands helps you see how databases can automate repetitive tasks.

2

Foundation

Basic syntax to create procedures

Concept: Shows how to write the SQL code to create a stored procedure.

In MySQL, you use CREATE PROCEDURE followed by the procedure name and the SQL commands inside BEGIN and END. For example: DELIMITER $$ CREATE PROCEDURE simpleProc() BEGIN SELECT 'Hello, world!'; END$$ DELIMITER ; This creates a procedure named simpleProc that shows a message.

Result

A stored procedure named simpleProc is created and ready to be called.

Knowing the syntax is the first step to writing your own stored procedures.

3

Intermediate

Calling and using stored procedures

🤔Before reading on: Do you think calling a stored procedure is done with SELECT or CALL? Commit to your answer.

Concept: Explains how to run stored procedures and get results.

To run a stored procedure in MySQL, you use the CALL command with the procedure name. For example: CALL simpleProc(); This runs the stored SQL commands inside the procedure. If the procedure returns data, you will see it as query results.

Result

The database executes the stored procedure and shows the output or performs actions.

Understanding the CALL command is key to using stored procedures effectively.

4

Intermediate

Using parameters in procedures

🤔Before reading on: Do you think stored procedures can accept inputs to change their behavior? Commit yes or no.

Concept: Introduces input parameters to make procedures flexible.

Stored procedures can take inputs called parameters. These let you pass values when calling the procedure. For example: DELIMITER $$ CREATE PROCEDURE greetUser(IN username VARCHAR(50)) BEGIN SELECT CONCAT('Hello, ', username, '!'); END$$ DELIMITER ; You call it with: CALL greetUser('Alice'); This will greet Alice specifically.

Result

The procedure uses the input to customize its output.

Parameters let stored procedures work like functions that adapt to different inputs.

5

Intermediate

Controlling flow inside procedures

🤔Before reading on: Can stored procedures include conditions like IF statements? Commit yes or no.

Concept: Shows how to add logic like IF and loops inside procedures.

You can use control flow statements inside stored procedures to make decisions or repeat actions. For example: DELIMITER $$ CREATE PROCEDURE checkNumber(IN num INT) BEGIN IF num > 0 THEN SELECT 'Positive'; ELSEIF num < 0 THEN SELECT 'Negative'; ELSE SELECT 'Zero'; END IF; END$$ DELIMITER ; This procedure checks if a number is positive, negative, or zero.

Result

The procedure returns different results based on the input value.

Adding logic inside procedures makes them powerful tools for complex database tasks.

6

Advanced

Error handling in stored procedures

🤔Before reading on: Do you think stored procedures automatically handle all errors? Commit yes or no.

Concept: Explains how to catch and manage errors inside procedures.

MySQL allows you to handle errors inside stored procedures using DECLARE HANDLER. For example: DELIMITER $$ CREATE PROCEDURE safeDivide(IN a INT, IN b INT) BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN SELECT 'Error: Division by zero or other issue'; END; SELECT a / b; END$$ DELIMITER ; This procedure catches division errors and shows a message instead of failing.

Result

The procedure runs safely and informs about errors without crashing.

Knowing error handling prevents unexpected crashes and improves reliability.

7

Expert

Performance and security considerations

🤔Before reading on: Do you think stored procedures always improve performance and security? Commit yes or no.

Concept: Discusses when stored procedures help or hurt performance and security.

Stored procedures can speed up repeated tasks by reducing network traffic and pre-compiling SQL. They also help secure data by controlling access. However, poorly written procedures can cause slowdowns or security holes if they allow SQL injection or misuse. Always test and review procedures carefully.

Result

You understand the tradeoffs and best practices for using stored procedures in real systems.

Recognizing the limits of stored procedures helps you avoid common pitfalls in production.

Under the Hood

When you create a stored procedure, the database saves the SQL commands in a special area. When you call it, the database runs these commands as a single unit. This reduces the need to send multiple commands over the network. The procedure can use variables, parameters, and control flow internally. The database engine parses and compiles the procedure once, then executes it efficiently many times.

Why designed this way?

Stored procedures were designed to improve efficiency and security. Before them, applications sent many separate SQL commands, causing slow communication and risk of errors. By grouping commands into procedures, databases reduce network traffic and centralize logic. This also allows database administrators to control access better and enforce rules consistently.

┌───────────────┐
│ CREATE PROC   │
│ (SQL saved)   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Stored in DB  │
│ procedure area│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ CALL procedure│
│ (execute SQL) │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Database runs │
│ SQL commands  │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think stored procedures always run faster than regular SQL queries? Commit yes or no.

Common Belief: Stored procedures always improve performance compared to running SQL queries directly.

Reality: Stored procedures can improve performance by reducing network traffic, but poorly written procedures or complex logic can slow down execution.

Why it matters: Assuming stored procedures are always faster can lead to ignoring query optimization and cause slow applications.

Quick: Do you think stored procedures automatically protect against all SQL injection attacks? Commit yes or no.

Common Belief: Using stored procedures means you don't have to worry about SQL injection attacks.

Reality: Stored procedures can help prevent SQL injection if used properly with parameters, but if they build SQL commands by concatenating strings, they remain vulnerable.

Why it matters: Believing stored procedures are automatically safe can cause security breaches and data loss.

Quick: Do you think stored procedures can return multiple result sets by default? Commit yes or no.

Common Belief: Stored procedures always return a single result set like a normal SELECT query.

Reality: Stored procedures can return multiple result sets or no results at all, depending on their internal SQL commands.

Why it matters: Misunderstanding this can cause confusion when handling procedure outputs in applications.

Quick: Do you think stored procedures are only useful for complex tasks? Commit yes or no.

Common Belief: Stored procedures are only needed for very complex database operations.

Reality: Stored procedures are useful even for simple repeated tasks to save time and reduce errors.

Why it matters: Ignoring stored procedures for simple tasks misses opportunities to improve code reuse and maintainability.

Expert Zone

1

Stored procedures can be nested, meaning one procedure can call another, allowing modular design.

2

The choice between stored procedures and application-side logic depends on factors like maintainability, performance, and security policies.

3

MySQL stored procedures have limitations such as no support for returning result sets directly like functions, requiring careful design.

When NOT to use

Avoid stored procedures when your application logic is better handled in the application layer for flexibility or when database portability is a priority. Use ORMs or application code for complex business logic that changes frequently.

Production Patterns

In production, stored procedures are used to enforce business rules, perform batch updates, and encapsulate complex queries. They are often combined with user permissions to restrict direct table access and improve security.

Connections

Functions in programming

Stored procedures are like functions that group instructions and can take inputs.

Understanding stored procedures as database functions helps bridge programming and database logic, making it easier to design reusable code.

API endpoints

Stored procedures act like backend API endpoints that perform specific tasks when called.

Seeing stored procedures as APIs clarifies their role in separating data logic from user interfaces and improving security.

Recipe books in cooking

Both store step-by-step instructions to repeat tasks reliably.

Recognizing this pattern across fields shows how saving and reusing instructions is a universal way to improve efficiency.

Common Pitfalls

#1 Trying to create a procedure without changing the delimiter.

Wrong approach:

CREATE PROCEDURE testProc() BEGIN SELECT 'Hi'; END;

Correct approach:

DELIMITER $$
CREATE PROCEDURE testProc() BEGIN SELECT 'Hi'; END$$
DELIMITER ;

Root cause:

MySQL uses semicolon as default statement end, so without changing delimiter, it ends procedure creation too early.

#2 Concatenating user input directly into SQL inside procedures.

Wrong approach:

CREATE PROCEDURE unsafeProc(IN name VARCHAR(50)) BEGIN SET @sql = CONCAT('SELECT * FROM users WHERE name = ''', name, ''''); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;

Correct approach:

CREATE PROCEDURE safeProc(IN name VARCHAR(50)) BEGIN SELECT * FROM users WHERE name = name; END;

Root cause:

Building SQL with string concatenation opens risk for SQL injection; using parameters avoids this.

#3 Assuming procedures return values like functions.

Wrong approach:

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

Correct approach:

CREATE PROCEDURE getCount(OUT total INT) BEGIN SELECT COUNT(*) INTO total FROM users; END;

Root cause:

MySQL procedures do not support RETURN for values; output parameters or result sets must be used.

Key Takeaways

Stored procedures are saved sets of SQL commands that you can run repeatedly by calling their name.

They help save time, reduce errors, and improve security by centralizing database logic.

You create stored procedures using CREATE PROCEDURE with optional input parameters and control flow.

Calling stored procedures uses the CALL command, and they can return results or perform actions.

Understanding their design and limitations helps you use stored procedures effectively and avoid common mistakes.

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