MySQL 5.0 Stored Procedures : Part 3

SO far we have looked at creating a simple procedure, then we added a couple of more lines to make it into a multiline procedure, but after all that our procedure is still a little useless. So let's look at how to make it a little more dynamic by passing in a value (or values) via a parameter.

Again I'll keep things simple, if you want to know more about parameters in programming in general pretty much any book on the subject of programming will have some sort of explanation so I'll leave you to do the research on that. But in essence parameters allow us to pass information in (and in the case of procedures, out) of our stored routines. "Hey, hold on" you all say.... "What's all this routine buisness". I suppose now is as good a time as any to introduce the word routine, so far we have been using the term "Stored Procedure" to mean program elements stored permanently in MySQL. But it's possible to use both functions and procedures, therefore it's good to have a term which covers both and make a distinction when talking explicitly about a procdure rather than routines in general.

...anyway back to parameters. So let's take our "hello world" example a stage further , lets say we wanted to say hello to anybody we chose. We could of course create a procedure for all our users, but a much better solution would be to pass in the name of the person we want to say hello to. I glossed over the () placed at the end of the procedure name in the first part, but it's this that we use to pass the parameters in and out of our routine. By default parameters are classed as IN parameters, this means the procedure will accept values into them, but we can also define them as OUT or even INOUT.

All we need to do is give the parameter a name and a datatype, the datatype can be any valid MySQL datatype that you would use for a column datatype in a table. Once it's defined we can then reference the parameter in our procedure using it's name.

mysql> delimiter //
mysql> create procedure 
    -> testParam(p_name varchar(30))
    -> begin
    ->    select concat('Hello ',p_name);
    -> end
    -> //
Query OK, 0 rows affected (0.06 sec)

mysql> call testParam('Bob')//
+-------------------------+
| concat('Hello ',p_name) |
+-------------------------+
| Hello Bob               |
+-------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

That's all there is to it. We can now just call the procedure using any name we like.

mysql> call testParam('Sue')//
+-------------------------+
| concat('Hello ',p_name) |
+-------------------------+
| Hello Sue               |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

OK, now for your home work.... This page has information on the different types of parameters.

http://mysql.gilfster.com/page.php?parent_id=1.1&page_id=1.1.6

In the next blog entry I'll be looking at interacting with the database using SQL inside the procedure. I promise that this is going somewhere usefull :)