WhereClause
In our last section SelectInto we had a situation where the procedure failed because we tried to fit the entire result set into a single variable. We said that there were a few ways to deal with this and one of those was to use a where clause. You may already be familiar with how to use a where clause in an SQL statement and it is done in exactly the same way within a procedure.
drop procedure if exists whereclauseproc // create procedure whereclauseproc(OUT p_out VARCHAR(30)) begin select emp_name into p_out from emps where emp_id = 1; end //
Download Whereproc1.myp
However what we can do within a procedure is change the where clause dynamically based on the value of a parameter we pass in. Lets say we wanted to be able to pass in the emp_id and get the employees name.
drop procedure if exists whereclauseproc // create procedure whereclauseproc(IN p_in INT, OUT p_out VARCHAR(30)) begin select emp_name into p_out from emps where emp_id = p_in; end //
Download Whereproc2.myp
This time we added an IN parameter to accept the emp_id we wanted to use. This was then used in the where clause instead of a hard coded value. To call the procedure we need to first set a user variable and pass that into the procedure.
set @a = 2 // Query OK, 0 rows affected (0.00 sec) call whereclauseproc(@a,@b) // Query OK, 0 rows affected (0.00 sec) select @b // +------+ | @b | +------+ | John | +------+ 1 row in set (0.00 sec)
Firstly we set the user variable @a to 2, the emp_id we wanted to find the name for. We then called the procedure using @a and @b (our out parameter) this returned the result into @b which we then selected to see the value. The next thing to do would be to try and run the procedure using a different emp_id to make sure the procedure really does return a different value based on the in parameter.
set @a = 3 // Query OK, 0 rows affected (0.00 sec) call whereclauseproc(@a,@b) // Query OK, 0 rows affected (0.02 sec) select @b // +------+ | @b | +------+ | Alan | +------+ 1 row in set (0.00 sec)
So we can see that using a different in parameter returns different restults. At this stage we won´t get into a discussion of security issues but this does give you some ideas about how you can make your MySQL data more secure, at the moment you may giving select access to users to stop them changing data, using procedures more normally functions we can add another layer of security. We can limit users to specific columns or even sub sets of columns by only giving them access to the procedures rather than the underlying tables. However this method of security has been addressed within version 5 of MySQL with the introduction of views.
We have digressed again so lets get back to programming stored procedures. There really isn´t a great deal more to say about where clauses at present, we have introduced the concept and hopefully you can see how you might use them to filter data. We should point out we have been using parameters directly in our examples but you could just as well use variables in the where clause.