MySQL Stored Procedure - UPDATE - Example
Source code to create and add sql update stored procedure to catalog
The following example is for creating a simple update stored procedure. You can run it through an explicit call from a host language program.
DROP PROCEDURE IF EXISTS `sp_students_UPDATE_byPK` ; CREATE PROCEDURE sp_students_UPDATE_byPK ( IN p_student_id INT(11) , IN p_active_flg TINYINT(4) ) BEGIN UPDATE students SET active_flg = p_active_flg WHERE student_id = p_student_id ; END ;
It is important to note that for every column you include in the update code you will need to enter data for. Typically, an sql update stored procedure would be created for all the columns. There would be a mapping layer that moves existing data to the exposed variables. In the example provided I have created an update stored procedure that could be tied directly to a function that would activate or inactivate a student. Having an sql update stored procedure for this type of activity is wholly rational and appropriate.
Executing the sql update stored procedure
Execute sql update stored procedure
To run the stored procedure you need to supply a values to the applicable variables.
/*** IN p_student_id INT(11) IN p_active_flg TINYINT(4) ***/ CALL sp_students_UPDATE_byPK ( 25 , 1 ) ;