MySQL - how to create a Stored Procedure
Creating StoredProcs still feels a little like magic at the moment, but it’s incredibly simple really.
Stored Procs (aka “SPs” or “SProcs”) live within the database and allow a number of activities to be chained together. They also represent an abstraction method so you can help de-couple DB and application code.
However, MySQL didn’t have Stored Procs until a year or two ago and because of this they’re not widespread.
The first thing to remember with MySQL Stored Procs is that the mysql engine will get confused if you write the SP and try and apply it using MySQL Query Browser or just using the mysql command line app; it’ll notice semicolons within your SP and try and apply those and it won’t work. Thus you need to change the delimiter before you define the Stored Proc. We do this by making it // instead of ; (a semicolon) using the “delimiter” command, we then swap it back to using semicolons at the end.
Here’s a standard bit of text that you can re-use as a template.
”
delimiter //
DROP PROCEDURE IF EXISTS xxx//
CREATE PROCEDURE xxx (IN param1 VARCHAR(30), OUT param2 VARCHAR(40))
BEGIN
DECLARE localvar VARCHAR(5);
SET localvar = 'helo';
SET param2 = (SELECT MD5(CONCAT(localvar, param1)) );
/* return the data here using a select statement (incase it's not easy for the caller to use the out param) */
SELECT param2 as md5hashedvarchar;
END;
//
delimiter ;
“