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

Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.