In this MySQL example, we have created stored procedure usp_GetEmployeeName which takes one IN and one OUT parameter. While calling this stored procedure, you need to pass two parameters, id and name. One would be  input parameter id and other would be output parameter to store result.

DELIMITER $$

USE `usersdb`$$

DROP PROCEDURE IF EXISTS `usp_GetEmployeeName`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_GetEmployeeName`(IN id INT, OUT e_name VARCHAR(20)) BEGIN DECLARE V_NAME VARCHAR(70); SELECT emp_name INTO V_NAME FROM employee WHERE emp_id = id; SET e_name = V_NAME; END$$

DELIMITER ;

Calling stored procedure:

call usp_GetEmployeeName(103, @name);

select @name;

 
Total Views 78, Views Today 2

2 thoughts on "Creating and Calling MySQL stored procedure with IN and OUT parameters"

  1. Enjoyed examining this, very good stuff, thanks .

  2. I must say, as a lot as I enjoyed reading what you had to say, I couldnt help but lose interest after a while.

Leave a Reply

Your email address will not be published. Required fields are marked *