Master MySQL Stored Procedures: Create, Call, and Debug Your First Procedure

One sentence to understand "Stored Procedure"

Stored procedure is: encapsulate a piece of SQL code so you can call it by name later.

Imagine you run a restaurant and each time a customer orders "stir‑fried eggs with potatoes", you have to manually prepare, cook, and season – tedious. A stored procedure is like a robot that completes the whole workflow when you say "make one stir‑fried egg with potatoes".

Environment (Hands‑on)

Recommended to practice while reading.

MySQL version: 8.0.44

Database client: DataGrip (or Navicat)

Test database:

testdb

All SQL executed in DataGrip SQL window

First goal: create the simplest stored procedure

Scenario

We write a procedure that simply prints a welcome message.

Correct SQL with comments

<code>-- Change delimiter to //
DELIMITER //

-- Create a procedure named hello
CREATE PROCEDURE hello()
BEGIN
  -- Output a welcome message
  SELECT '你好，欢迎关注 IT咸鱼！';
END //

-- Restore delimiter
DELIMITER ;
</code>

Key explanations

DELIMITER //

: change statement delimiter to avoid conflicts with internal semicolons.

CREATE PROCEDURE hello()

: creates a procedure named

hello

.

BEGIN ... END

: marks the start and end of the statement block.

SELECT '...'

: outputs a string.

DELIMITER ;

: restores the default delimiter.

How to execute it?

<code>CALL hello();
</code>

Running this in DataGrip shows the output:

<code>+-----------------------------------+
| 你好，欢迎关注 IT咸鱼！ |
+-----------------------------------+
</code>

Where is the procedure stored? How to view it?

Use the following commands:

<code>SHOW PROCEDURE STATUS WHERE Db='testdb';
SHOW CREATE PROCEDURE hello;
</code>

Common beginner mistakes

Error near 'END;': forgot to set

DELIMITER //

first.

No output: forgot to execute

CALL hello();

.

Permission error: account lacks

CREATE ROUTINE

privilege.

Summary

Goal: create the first stored procedure.

Key commands:

CREATE PROCEDURE

,

CALL

,

DELIMITER

.

Result:

SELECT

prints a line.

Suitable for absolute beginners.

Practical suggestion

Try creating a procedure that shows today’s date:

<code>DELIMITER //
CREATE PROCEDURE show_today()
BEGIN
  SELECT CURDATE() AS 今天日期;
END //
DELIMITER ;
</code>

Execute:

<code>CALL show_today();
</code>

Output example:

<code>+------------+
| 今天日期 |
+------------+
| 2025-06-17 |
+------------+
</code>

Next step: parameters

The next article will cover IN/OUT/INOUT parameters, how to pass values, and an example that greets a user by name.