Category Archives: Database

MySQL Procedure Part1

Posted on by | Leave a reply

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>
[code lang="sql"]
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