Call Stored Procedure input output parameter

Call Stored Procedure input output parameter

This example will show you how to call Stored Procedure with one input and one output parameter. For this example we are using MySQL server and will call this procedure using MySQL Workbench (We can use any tool to call it)

• Sample stored procedure:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $

CREATE DEFINER=`root`@`localhost` PROCEDURE `SampleProcedure`
(IN deptId VARCHAR(50), OUT departmentName VARCHAR(50))

BEGIN

SELECT *  FROM department where DepartmentID = deptId;
set departmentName = 33;

END

• To call stored procedure:

CALL `javahonk`.`SampleProcedure`('33', @departmentName);
SELECT @departmentName;

• Output:

• And if you use below:

CALL `javahonk`.`SampleProcedure`('33', @departmentName);

• Output will be select statement result from the table as below:

For more information please refer this link