Creating stored procedures in MySQL

Introduction

Stored procedures help you save a set of SQL commands to run later easily. They make repeating tasks faster and keep your work organized.

You want to run the same SQL commands many times without typing them again.

You need to perform a complex task that involves multiple SQL steps.

You want to share a common task with others without giving them the full SQL code.

You want to improve performance by reducing repeated parsing of SQL commands.

You want to keep your database logic in one place for easier updates.

Syntax

MySQL

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

Use CREATE PROCEDURE to start making a stored procedure.

Parameters are optional and can be used to pass values into the procedure.

Examples

A simple procedure that returns a greeting message.

MySQL

CREATE PROCEDURE simpleProcedure()
BEGIN
  SELECT 'Hello, world!';
END

A procedure that takes a user ID and returns the matching user record.

MySQL

CREATE PROCEDURE getUserById(IN userId INT)
BEGIN
  SELECT * FROM users WHERE id = userId;
END

A procedure that adds two numbers and returns the result using an OUT parameter.

MySQL

CREATE PROCEDURE addNumbers(IN a INT, IN b INT, OUT sum INT)
BEGIN
  SET sum = a + b;
END

Sample Program

This creates a procedure named getProductPrice that takes a product ID and returns its price. Then it calls the procedure with product ID 2.

MySQL

DELIMITER $$
CREATE PROCEDURE getProductPrice(IN productId INT)
BEGIN
  SELECT price FROM products WHERE id = productId;
END$$
DELIMITER ;

CALL getProductPrice(2);

Important Notes

Remember to change the delimiter when creating procedures to avoid conflicts with semicolons inside the procedure.

Use CALL procedure_name(parameters); to run a stored procedure.

Stored procedures can help keep your database tasks organized and reusable.

Summary

Stored procedures save SQL commands to run later easily.

They can take input parameters and return results.

Use CREATE PROCEDURE and CALL to create and run them.