How to create and call stored procedure in MySQL with IN and OUT parameters
Create and Call MySQL stored procedure with IN Parameters Here is the command to create a MySQL stored procedure with one IN parameter , here we are getting total number of employee by department, dept_id is a foreign key from department table. mysql > DELIMITER // mysql > create procedure usp_totalEmployeeByDeparment ( IN id INT ) -> begin -> select count (*) as total from employee where dept_id = id ; -> end // Query OK , 0 rows affected ( 0.00 sec ) mysql > DELIMITER ; We have first changed delimiter as // to mark end of stored procedure, and then reverted it back to previous delimiter. Also using “usp” as prefix for user defined stored procedure is one of the SQL best practices to separate system and user stored procedures. Now you can call this stored procedure from MySQL command prompt as : mysql > call usp_totalEmployeeByDeparment ( 2 ); + -------+ | total | + -------+ |