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=`cpses_adig9ym3e7`@`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;

 

(Visited 25 times, 1 visits today)

3 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.

  3. I simply want to say I am new to blogging and site-building and actually loved you’re blog. Very likely I’m going to bookmark your site . You absolutely have good article content. Cheers for sharing with us your blog.

Leave a Reply

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