MySQL Procedure

MySQL Procedure

Create, call and drop MySQL procedure syntax and example.

MySQL Create procedure syntax

CREATE OR REPLACE PROCEDURE
proc_name([ IN | OUT | INOUT ] parameter_name datatype)
BEGIN
procedure_body
END;

Create procedure example

CREATE OR REPLACE PROCEDURE 
proc_test(IN p_id int, OUT p_name varchar(100))
BEGIN
	SELECT name INTO p_name 
	FROM test WHERE ID=p_id;
	insert into test2(id, name) 
	values (p_id, p_name);
END;

Output

PROCEDURE PROC_TEST compiled

Call procedure example

CALL proc_test (1,@variable_name);
SELECT @variable_name;

Output

proc_test 1,@VARIABLE_NAME) succeeded.

Drop procedure example

DROP PROCEDURE proc_test;

Output

procedure PROC_TEST dropped.