MySQL Stored Procedure: Syntax, Examples, and Use

A MySQL stored procedure is a saved block of SQL statements that you can call by name, passing parameters to perform a specific task. You create it once with CREATE PROCEDURE, then invoke it anytime with CALL procedure_name(), which saves network traffic and centralizes logic on the server. This article explains the syntax, how to write your first procedure, common pitfalls, and when they are worth using.

What Is a Stored Procedure in MySQL?

Think of a stored procedure as a function or script that lives inside the database server. Instead of sending five separate queries from your application, you send one CALL and the server runs all five queries in sequence. The procedure can accept input values, return result sets, and even modify data. MySQL has supported stored procedures since version 5.0, so they are a mature, standard feature.

A procedure differs from a stored function in one key way: functions return a single value and can be used inside expressions like SELECT my_function(x), while procedures do not return a value directly—they produce result sets or manipulate data, and you call them with CALL.

Basic Syntax and Structure

The core syntax is straightforward:

CREATE PROCEDURE procedure_name (parameter_list)
BEGIN
    -- SQL statements
END

The parameter list can contain three types:

• IN – the caller passes a value into the procedure (default).
• OUT – the procedure assigns a value that the caller can read after the call.
• INOUT – the caller passes a value, and the procedure can modify it.

Here is a minimal working example that selects all customers from a table:

CREATE PROCEDURE GetAllCustomers()
BEGIN
    SELECT * FROM customers;
END

To run it: CALL GetAllCustomers();

To delete it when no longer needed: DROP PROCEDURE GetAllCustomers;

Writing Your First Procedure with Parameters

Let’s create a procedure that returns customers from a specific city, using an IN parameter:

CREATE PROCEDURE GetByCity(IN city_name VARCHAR(50))
BEGIN
    SELECT * FROM customers WHERE city = city_name;
END

Call it with: CALL GetByCity('London');

Now an example with an OUT parameter that counts rows:

CREATE PROCEDURE CountByCity(IN city_name VARCHAR(50), OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM customers WHERE city = city_name;
END

To call it and see the result, you need a session variable:

CALL CountByCity('London', @count);
SELECT @count;

Notice the @count is a user-defined variable that persists for the session, letting you retrieve the output value.

Control Flow: IF, CASE, and Loops

Procedures become powerful when you add logic. MySQL supports standard branching and looping constructs. Here is an IF example that returns a message based on a stock level:

CREATE PROCEDURE CheckStock(IN product_id INT, IN qty INT)
BEGIN
    DECLARE stock INT;
    SELECT quantity_in_stock INTO stock FROM products WHERE id = product_id;
    IF stock >= qty THEN
        SELECT 'In stock' AS status;
    ELSE
        SELECT 'Insufficient stock' AS status;
    END IF;
END

You can also use LOOP, WHILE, and REPEAT for iteration. A simple WHILE loop that inserts numbers into a temp table:

CREATE PROCEDURE FillNumbers(IN max_num INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= max_num DO
        INSERT INTO numbers (value) VALUES (i);
        SET i = i + 1;
    END WHILE;
END

Always remember to declare variables with DECLARE at the beginning of the BEGIN block, before any other statements.

Common Mistakes and How to Avoid Them

Even experienced developers trip over a few recurring issues:

• Forgetting the delimiter change. In the MySQL command-line client, the default statement delimiter is ;, which conflicts with the semicolons inside the procedure body. You must change it before creating a procedure: DELIMITER // … END// … then DELIMITER ;. Many GUI tools (like MySQL Workbench) handle this automatically, but the raw client does not.
• Using SELECT instead of SELECT INTO for variables. If you write SELECT quantity INTO stock but the query returns more than one row, MySQL throws an error. Use LIMIT 1 or ensure the query returns exactly one row.
• Not handling null values. If a column is NULL, comparisons like IF stock >= qty evaluate as NULL (not true or false), so the ELSE branch runs. Use IFNULL() or COALESCE() where needed.
• Overusing procedures. For simple single queries, a procedure adds overhead and makes the code harder to version-control. Use them for multi-step operations or business logic that must be consistent across applications.

Stored Procedures vs. Prepared Statements vs. Functions

It’s easy to confuse these three features:

• Stored procedure – saved on the server, can contain multiple statements, control flow, and transactions. Called with CALL.
• Prepared statement – a client-side or server-side template that you prepare once and execute multiple times with different parameters. It is not saved as a database object; it exists only for the session.
• Stored function – returns a single value and can be used inside SQL expressions. Cannot modify tables (with exceptions for temporary tables), while procedures can.

As a rule of thumb: use a procedure when you need to perform a sequence of actions or return multiple result sets; use a function when you need a reusable calculation in a query; use a prepared statement when you just want to avoid re-parsing the same query repeatedly.

Security and Performance Considerations

Stored procedures can improve security by letting you grant a user permission to execute a procedure without granting direct table access. For example, a user could have EXECUTE on GetByCity but no SELECT on the customers table. This limits what the user can see to only what the procedure returns.

Performance-wise, procedures reduce network round trips because multiple statements are sent in one call. However, MySQL does not compile procedures into machine code; it interprets them each time, so complex logic may not be faster than equivalent application code. For heavy data processing, a procedure can be faster because it avoids moving data to the client and back.

One important caveat: stored procedures can hide complexity, making debugging harder. Always keep the procedure code in your version control system (as a .sql file) and document what each one does.

Frequently Asked Questions

How do I see a list of all stored procedures in a database?

Run SHOW PROCEDURE STATUS WHERE Db = 'your_database'; or query the information_schema.ROUTINES table. To see the code of a specific procedure, use SHOW CREATE PROCEDURE procedure_name;

Can I call a stored procedure from within another stored procedure?

Yes. Just use CALL other_procedure(); inside the body. The called procedure runs, and control returns to the caller. This is a good way to build modular logic.

What is the difference between a stored procedure and a trigger?

A trigger is a special kind of stored program that automatically executes in response to an INSERT, UPDATE, or DELETE on a specific table. You cannot call a trigger directly; the database fires it. A stored procedure is explicitly invoked with CALL.

Can a stored procedure return multiple result sets?

Yes. If the procedure contains multiple SELECT statements, each one produces a result set. In MySQL Workbench, you will see multiple tabs with results. In application code (like PHP PDO or Python), you need to fetch each result set in sequence.

Why do I get a syntax error when creating a procedure?

Almost always because the delimiter is not set correctly. If you are using the mysql command line, type DELIMITER // before CREATE PROCEDURE and use // after END, then reset with DELIMITER ;. Also check that every BEGIN has a matching END and that variable declarations come first.

Are stored procedures portable to other databases?

Not without changes. Each database (PostgreSQL, SQL Server, Oracle) has its own syntax for procedures and its own procedural language (PL/pgSQL, T-SQL, PL/SQL). The basic concept is the same, but you will need to rewrite the code if you migrate.

Related

• Google Wallet: Receipts, Passes, Cards & Device Support
• Jio World Record: 5G Speed Test in India
• Improve Video Quality in VLC: Picture Settings and Fixes