How to Create Stored Procedure in MySQL: Syntax and Example

To create a stored procedure in MySQL, use the CREATE PROCEDURE statement followed by the procedure name and body enclosed in BEGIN ... END. You can define input parameters and write SQL statements inside the procedure to perform tasks.

Syntax

The basic syntax to create a stored procedure in MySQL includes the procedure name, optional parameters, and the procedure body enclosed in BEGIN ... END. You use CREATE PROCEDURE followed by the procedure name and parameters. Inside the body, you write SQL statements that the procedure will execute.

• CREATE PROCEDURE: starts the procedure definition
• procedure_name: the name you give your procedure
• (parameters): optional input parameters with types
• BEGIN ... END: block containing SQL statements

sql

CREATE PROCEDURE procedure_name ([IN|OUT|INOUT] param_name datatype, ...)
BEGIN
  -- SQL statements
END

Example

This example creates a stored procedure named GetTotalSales that takes a product ID as input and returns the total sales amount for that product from a sales table.

sql

DELIMITER $$
CREATE PROCEDURE GetTotalSales(IN productId INT)
BEGIN
  SELECT SUM(amount) AS TotalSales
  FROM sales
  WHERE product_id = productId;
END$$
DELIMITER ;

Output

When called with a product ID, the procedure returns a single row with the total sales amount for that product.

Common Pitfalls

Common mistakes when creating stored procedures include forgetting to change the delimiter, missing BEGIN ... END for multiple statements, and not specifying parameter modes (IN, OUT, INOUT) correctly.

For example, if you forget to change the delimiter, MySQL will think the procedure ends too early and give an error.

sql

/* Wrong: Missing delimiter change causes error */
CREATE PROCEDURE WrongProc()
BEGIN
  SELECT 'Hello';
  SELECT 'World';
END;

/* Right: Change delimiter before and after */
DELIMITER $$
CREATE PROCEDURE RightProc()
BEGIN
  SELECT 'Hello';
  SELECT 'World';
END$$
DELIMITER ;

Quick Reference

Keyword	Description
CREATE PROCEDURE	Starts the procedure definition
procedure_name	Name of the stored procedure
IN	Input parameter
OUT	Output parameter
INOUT	Input and output parameter
BEGIN ... END	Block to group multiple SQL statements
DELIMITER	Changes statement delimiter to allow procedure creation

Key Takeaways

Use CREATE PROCEDURE with BEGIN ... END to define stored procedures in MySQL.

Always change the delimiter before and after creating a procedure to avoid syntax errors.

Specify parameter modes (IN, OUT, INOUT) clearly for procedure inputs and outputs.

Write SQL statements inside the procedure body to perform the desired operations.

Test your procedure by calling it with appropriate parameters to verify results.