Example of a MySQL stored procedure with Parameters

A small example of MySQL stored procedure with parameters the Stored Procedure P_GET_CLASS_NAME is based on the input class number to determine the class name Stored Procedure P_INSERT_STUDENT is to receive input student information, and finally insert information into the student table. Www.2cto.com [SQL] DROP PROCEDURE IF EXISTS 'P _ GET_CLASS_NAME '; CREATE PROCEDURE P_GET_CLASS_NAME (IN ID int, OUT NAME VARCHAR (50) BEGIN IF (ID = 1) then set name = 'first class'; end if; IF (ID = 2) then set name = 'second class'; end if; END; drop procedure if exists 'P _ INSERT_STUDENT '; create procedure P_INSERT_STUDENT (in id int, in name varchar (10), in classno int, in birth datetime) begin set @ ID = ID; SET @ NAME = NA ME; SET @ CLASSNO = CLASSNO; SET @ BIRTH = BIRTH; SET @ CLASSNAME = NULL; CALL P_GET_CLASS_NAME (@ CLASSNO, @ CLASSNAME ); SET @ insertSql = CONCAT ('insert INTO TBL_STUDENT VALUES (?,?,?,?) '); PREPARE stmtinsert FROM @ insertSql; EXECUTE stmtinsert USING @ ID, @ NAME, @ CLASSNAME, @ BIRTH; deallocate prepare stmtinsert; END; CALL P_INSERT_STUDENT (1, 'xu Yue ', 1, '2017-10-01 10:20:01 '); in the second stored procedure, ① the parameter is declared using SET, the first stored procedure is called. ② the NAME parameter in the first stored procedure is the output parameter, therefore, the @ CLASSNAME parameter is assigned a value after the first process is called. ③ use CONCAT to concatenate an SQL statement and pass in the parameter to execute the SQL statement CALL P_INSERT_STUDENT (1, 'xu Yue ', 1, '2017-10-01 10:20:01 '); call the Stored Procedure