DROP PROCEDURE IF EXISTS while1;
CREATE PROCEDURE while1()
BEGIN
DECLARE v_max INT DEFAULT 15;
DECLARE v_counter INT DEFAULT 0;
WHILE v_counter < v_max do
SELECT v_counter;
SET v_counter=v_counter+1;
END WHILE;
END;
Category Archives: Procedures
Mysql Procedure Taking parameter
DROP PROCEDURE IF EXISTS proc_sample1;
CREATE PROCEDURE proc_sample1(IN pFruitId VARCHAR(255))
BEGIN
SET @strSQL = 'SELECT fruit_name
FROM fruits
WHERE fruit_id = ?';
SET @pFruitId = pFruitId;
PREPARE stmt FROM @strSQL;
EXECUTE stmt USING @pFruitId;
DEALLOCATE PREPARE stmt;
END
Building Dynamic Query in Mysql Stored Procedure
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
MySQL Procedure Part1
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