602SQL Documentation Index  

SELECT INTO Statement

This statement sends results from a query that contains one row into variables.

The SELECT INTO statement has the same syntax as the query specification, however the INTO clause is present between the SELECT and FROM clauses in this form:

clause_INTO ::= INTO variable {, variable }

The number of variables must be the same as the number of expressions in the SELECT clause, and the types must allow assigning the SELECT clause expression values to them. A variable may also be a client variable prefaced by a colon (or :>). A variable may also be the expression new_row.column when used in a INSERT or UPDATE trigger.

If the query result contains no record or more than one record, the error sqlstate 21000 (SQ_CARDINALITY_VIOLATION) occurs. If the CONTINUE HANDLER exists for this exception, it is possible to assign new variable values inside this handler so the procedure may continue.

Example:

FUNCTION maxnumber () RETURNS INT;  
BEGIN  
 DECLARE maxval INT;  
 SELECT MAX(number)+1 INTO maxval FROM Tab1;  
 RETURN (maxval);  
END