Check input parameter : Parameters « Procedure Function « MySQL Tutorial






mysql>
mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->              values (1,'Jason',    'Martin',  '19960725',  '20060725', 1234.56, 'Toronto',  'Programmer');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(2,'Alison',   'Mathews',  '19760321', '19860221', 6661.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(3,'James',    'Smith',    '19781212', '19900315', 6544.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(4,'Celia',    'Rice',     '19821024', '19990421', 2344.78, 'Vancouver','Manager');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(5,'Robert',   'Black',    '19840115', '19980808', 2334.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(6,'Linda',    'Green',    '19870730', '19960104', 4322.78,'New York',  'Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(7,'David',    'Larry',    '19901231', '19980212', 7897.78,'New York',  'Manager');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(8,'James',    'Cat',     '19960917',  '20020415', 1232.78,'Vancouver', 'Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.02 sec)

mysql>
mysql>
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE sp_customer_search_dyn
    ->     (in_Description VARCHAR(30),
    ->      in_contact_surname VARCHAR(30),
    ->      in_contact_firstname VARCHAR(30),
    ->      in_city VARCHAR(10))
    ->
    -> BEGIN
    ->   DECLARE l_where_clause VARCHAR(1000) DEFAULT 'WHERE';
    ->
    ->   IF in_Description IS NOT NULL THEN
    ->       SET l_where_clause=CONCAT(l_where_clause,
    ->          ' description="',in_Description,'"');
    ->   END IF;
    ->   select l_where_clause;
    ->   IF in_contact_surname IS NOT NULL THEN
    ->      IF l_where_clause<>'WHERE' THEN
    ->         SET l_where_clause=CONCAT(l_where_clause,' AND ');
    ->      END IF;
    ->      SET l_where_clause=CONCAT(l_where_clause,
    ->          ' last_name="',in_contact_surname,'"');
    ->   END IF;
    ->   select l_where_clause;
    ->
    ->   IF in_contact_firstname IS NOT NULL THEN
    ->      IF l_where_clause<>'WHERE' THEN
    ->         SET l_where_clause=CONCAT(l_where_clause,' AND ');
    ->      END IF;
    ->      SET l_where_clause=CONCAT(l_where_clause,
    ->          ' first_name="',in_contact_firstname,'"');
    ->   END IF;
    ->   select l_where_clause;
    ->
    ->   IF in_city IS NOT NULL THEN
    ->      IF l_where_clause<>'WHERE' THEN
    ->         SET l_where_clause=CONCAT(l_where_clause,' AND ');
    ->      END IF;
    ->      SET l_where_clause=CONCAT(l_where_clause,
    ->          ' city="',in_city,'"');
    ->   END IF;
    ->   select l_where_clause;
    ->
    ->   SET @sql=CONCAT('SELECT * FROM employee ', l_where_clause);
    ->
    ->   select @sql;
    ->
    ->   PREPARE s1 FROM @sql;
    ->   EXECUTE s1;
    ->   DEALLOCATE PREPARE s1;
    ->
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> call sp_customer_search_dyn('Tester','Martin','Jason','Toronto');
+----------------------------+
| l_where_clause             |
+----------------------------+
| WHERE description="Tester" |
+----------------------------+
1 row in set (0.02 sec)

+----------------------------------------------------+
| l_where_clause                                     |
+----------------------------------------------------+
| WHERE description="Tester" AND  last_name="Martin" |
+----------------------------------------------------+
1 row in set (0.02 sec)

+----------------------------------------------------------------------------+
| l_where_clause                                                             |
+----------------------------------------------------------------------------+
| WHERE description="Tester" AND  last_name="Martin" AND  first_name="Jason" |
+----------------------------------------------------------------------------+
1 row in set (0.02 sec)

+------------------------------------------------------------------------------------------------+
| l_where_clause                                                                                 |
+------------------------------------------------------------------------------------------------+
| WHERE description="Tester" AND  last_name="Martin" AND  first_name="Jason" AND  city="Toronto" |
+------------------------------------------------------------------------------------------------+
1 row in set (0.24 sec)

+-----------------------------------------------------------------------------------------------------------------------+
| @sql                                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------+
| SELECT * FROM employee WHERE description="Tester" AND  last_name="Martin" AND  first_name="Jason" AND  city="Toronto" |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.24 sec)

Empty set (0.24 sec)

Query OK, 0 rows affected (0.24 sec)

mysql>
mysql> drop procedure sp_customer_search_dyn;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)








11.7.Parameters
11.7.1.DateTime parameter
11.7.2.Verify the input parameter
11.7.3.Check input parameter
11.7.4.Declare and use the OUT parameter
11.7.5.Save status to an OUT parameter
11.7.6.Pass status code and message out of a procedure
11.7.7.Using OUT parameter to return the status code and message from a procedure
11.7.8.Pass variable to a procedure as the OUT parameter