By creating SP for application, we can create our logic seperate from the application. The main advantage for using this is we can reuse the code for differenct languages with ease.
Ex:
CREATE PROCEDURE `sp_user`(
id int,
name varchar(64),
type_id int,
OUT out_status varchar(10)
)
MODIFIES SQL DATA
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
SET out_status = 'error';
IF id > 0 THEN
//Call the update query
ELSE
// Call the insert query
END IF;
SELECT out_status;
END;
You can call the procedures using the command : CALL `sp_user`(0,'user1','1',@out_status)