Using SELECT Statements with an INTO Clause

If you have a SELECT statement that returns only a single row, you can return that row into stored program variables by using the INTO statement within the SELECT statement. The format for such a SELECT is:

 SELECT expression1 [, expression2 ....]
 INTO variable1 [, variable2 ...]
 other SELECT statement clauses

Example 5-2 shows how we can retrieve details from a single customer. The customer ID is passed in as a parameter.

Example 5-2. Using a SELECT-INTO statement

CREATE PROCEDURE get_customer_details(in_customer_id INT)
BEGIN
 DECLARE l_customer_name VARCHAR(30);
 DECLARE l_contact_surname VARCHAR(30);
 DECLARE l_contact_firstname VARCHAR(30);

 SELECT customer_name, contact_surname,contact_firstname
 INTO l_customer_name,l_contact_surname,l_contact_firstname
 FROM customers
 WHERE customer_id=in_customer_id;

 /* Do something with the customer record */

END;

If the SQL statement returns more than one row, a runtime error will result. For instance, if we omitted the WHERE clause in Example 5-2, the following error would result when we tried to run the stored procedure:

mysql> CALL get_customer_details(2) ;
ERROR 1172 (42000): Result consisted of more than one row