Table Structure

CREATE TABLE Areas(AreaName VARCHAR(255),
                    PinCode VARCHAR(255))
                    
INSERT INTO Areas(AreaName, PinCode)VALUES
                 ('Teynampet', '6000018'), 
                 ('Ramapuram', '6000089'),
                 ('TNagar', '6000017'), 
                 ('Mylapore', '6000014'), 
                 ('Gopalapuram', '6000087')

Stored Procedure

DROP PROCEDURE IF EXISTS mp_test;
CREATE PROCEDURE mp_test(IN pArea VARCHAR(255))
   BEGIN
      SET @Query = '';
      SET @City  = '';
      SET @Query = 'SELECT PinCode FROM Areas';

      IF pArea != '' THEN
         SET @City = CONCAT(' WHERE AreaName = "', pArea, '"');
      END IF;
      
      SET @Query = CONCAT(@Query, @City);

 PREPARE stmt FROM @Query;
 
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;  
END 

A stored procedure, by definition, is a segment of declarative SQL code which is stored in the database catalog and can be invoked later by a program, a trigger or even a stored procedure.

Now let us see how to Create stored procedure in mysql
Code for Simple Procedure

DELIMITER //
CREATE PROCEDURE Sample()
BEGIN
  SELECT 'Hello World !';
END//

Calling Procedure

CALL Sample()

<strong>Deleting Procedure if Already exists</strong>

DELIMITER //
DROP PROCEDURE  IF EXISTS ShowPages;
CREATE PROCEDURE ShowPages()
BEGIN
  SELECT *
    FROM Pages;
END

Supplying a Argument to Procedure

DELIMITER //
DROP PROCEDURE  IF EXISTS ShowPages;
CREATE PROCEDURE ShowPages(IN Var1 VARCHAR(30))
BEGIN
  SELECT *
    FROM Pages
   WHERE PageName = Var1;
END

Working with Supplied Argument

DELIMITER //
DROP PROCEDURE  IF EXISTS ShowPages;
CREATE PROCEDURE ShowPages(IN Var1 INT)
BEGIN
   SELECT Var1 + 10;
END

Calling Procedure

CALL ShowPages(10)

O/P:
20

Getting output from Procedure

DELIMITER //
DROP PROCEDURE  IF EXISTS ShowPages;
CREATE PROCEDURE ShowPages(OUT Var1 VARCHAR(30))
BEGIN
  SET Var1 = 'Mugilvannan';
  SELECT Var1;
END//

Calling Procedure

CALL ShowPages(@parameter_var1);

O/P:
Mugilvannan

Returns The Last Inserted ID

DELIMITER //
DROP PROCEDURE  IF EXISTS  InsertSubjects;
CREATE PROCEDURE InsertSubjects(OUT Id INT, IN strMenuName VARCHAR(30),IN strPosition INT, IN strVisible TINYINT)
BEGIN
  INSERT INTO Subjects(MenuName, Position, Visible)
VALUES(strMenuName, strPosition, strVisible);

 SELECT LAST_INSERT_ID()
   FROM Subjects
  LIMIT 1;
END//

CALLING ONE PROCEDURE WITHIN ANOTHER PROCEDURE
Procedure1 mp_proc1 Will return value returned by procedure 2

//PROCEDURE 1
DROP PROCEDURE IF EXISTS mp_proc1;
CREATE PROCEDURE mp_proc1(OUT AmenityName1 VARCHAR(100))
BEGIN
 CALL mp_proc2(@amnName);
 SET AmenityName1 = (SELECT @amnName); 
END

//PROCEDURE 2
DROP PROCEDURE IF EXISTS mp_proc2;
CREATE PROCEDURE mp_proc2(OUT AmenityName VARCHAR(100))
BEGIN
 SET AmenityName =(SELECT amenity_name 
                     FROM mp_amenities
                    WHERE id = 2);
END