mysql> call sp_customer_search_dyn('Tester','Smith','James','Vancouver');
+----------------------------+
| l_where_clause |
+----------------------------+
| WHERE description="Tester" |
+----------------------------+
1 row in set (0.00 sec)
+---------------------------------------------------+
| l_where_clause |
+---------------------------------------------------+
| WHERE description="Tester" AND last_name="Smith" |
+---------------------------------------------------+
1 row in set (0.00 sec)
+---------------------------------------------------------------------------+
| l_where_clause |
+---------------------------------------------------------------------------+
| WHERE description="Tester" AND last_name="Smith" AND first_name="James" |
+---------------------------------------------------------------------------+
1 row in set (0.02 sec)
+-------------------------------------------------------------------------------
------------------+
| l_where_clause
|
+-------------------------------------------------------------------------------
------------------+
| WHERE description="Tester" AND last_name="Smith" AND first_name="James" AND
city="Vancouver" |
+-------------------------------------------------------------------------------
------------------+
1 row in set (0.03 sec)

+-------------------------------------------------------------------------------
-----------------------------------------+
| @sql
|
+-------------------------------------------------------------------------------
-----------------------------------------+
| SELECT * FROM employee WHERE description="Tester" AND last_name="Smith" AND
first_name="James" AND city="Vancouver" |
+-------------------------------------------------------------------------------
-----------------------------------------+
1 row in set (0.06 sec)
+------+------------+-----------+------------+------------+---------+-----------
+-------------+
| id | first_name | last_name | start_date | end_date | salary | city
| description |
+------+------------+-----------+------------+------------+---------+-----------
+-------------+
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver
| Tester |
+------+------------+-----------+------------+------------+---------+-----------
+-------------+
1 row in set (0.08 sec)
Query OK, 0 rows affected (0.09 sec)

sp还可以捕获错误做出正确的处理:

mysql> delimiter $$
mysql> CREATE PROCEDURE myProc11
-> (in_first_name VARCHAR(30),
-> in_last_name VARCHAR(30),
-> in_city VARCHAR(30),
-> in_description VARCHAR(10),
-> OUT out_status VARCHAR(30))
-> MODIFIES SQL DATA
-> BEGIN
-> DECLARE CONTINUE HANDLER FOR 1406
-> SET out_status="desc is to long";
->
-> SET out_status='OK';
-> INSERT INTO employee
-> (first_name,last_name,city,description)
-> VALUES
-> (in_first_name,in_last_name,in_city,in_description);
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> set @myMessage = 0;
Query OK, 0 rows affected (0.00 sec)