Syntax for Creating a Stored Procedure
DELIMITER //
CREATE PROCEDURE procedure_name (IN param1 data_type, OUT param2 data_type)
BEGIN
-- SQL statements
END //
DELIMITER ;
# IN specifies an input parameter.
# OUT specifies an output parameter.
# INOUT specifies both input and output parameters.DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
SELECT * FROM employees;
END //
DELIMITER ;
# To call this stored procedure, you would use:
CALL GetAllEmployees();
Stored Procedure with Parameters
USE `world`;
DROP procedure IF EXISTS `world`.`CalculateTotal`;
DELIMITER $$
USE `world`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `CalculateTotal`(
IN price DECIMAL(10, 2),
IN quantity INT,
OUT Output1 Decimal(10,2),
OUT Output2 Decimal(10,2)
)
BEGIN
DECLARE total DECIMAL(10, 2);
SET Output2 = price * quantity;
SELECT price * quantity into Output1;
END$$
DELIMITER;set @Output1 = 0;
set @Output2 = 0;
call world.CalculateTotal(10, 20, @Output1, @Output2);
select @Output1, @Output2;