? MySQL Stored Procedures

? Quick Overview

A Stored Procedure in MySQL is a named block of SQL statements stored on the database server. It allows developers to group SQL logic together and execute it repeatedly using a single command. Stored procedures reduce application-side SQL, improve performance, and enforce consistent logic.

? Key Concepts

• Stored procedures are created using CREATE PROCEDURE
• They support input (IN), output (OUT), and input-output (INOUT) parameters
• Control-flow statements such as IF, CASE, and loops are supported
• Executed using the CALL statement
• Help improve security by restricting direct table access

? Syntax / Theory

Because stored procedures contain multiple SQL statements, MySQL requires a custom delimiter during creation. This prevents the server from prematurely ending the procedure definition.

-- Basic stored procedure syntax
DELIMITER //

CREATE PROCEDURE procedure_name()
BEGIN
SELECT 'Hello from Stored Procedure';
END //

DELIMITER ;

? Code Example(s)

The following procedure retrieves all records from a table named users. This is commonly used for reusable reporting logic.

-- Stored procedure to return all users
DELIMITER //

CREATE PROCEDURE get_all_users()
BEGIN
SELECT * FROM users;
END //

DELIMITER ;

Stored procedures can also accept parameters to make them dynamic.

-- Stored procedure with input parameter
DELIMITER //

CREATE PROCEDURE get_user_by_id(IN uid INT)
BEGIN
SELECT * FROM users WHERE id = uid;
END //

DELIMITER ;

? Live Output / Explanation

-- Executing a stored procedure
CALL get_all_users();