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.