SELECT (Procedures)
Go Up to Procedures and Triggers
Retrieves a single row that satisfies the requirements of the search condition. The same as standard singleton SELECT, with some differences in syntax. Available in triggers and stored procedures.
<select_expr> = <select_clause> <from_clause> [<where_clause>] [<group_by_clause>] [<having_clause>] [<union_expression>] [<plan_clause>] [<ordering_clause>] <into_clause>;
Description: In a stored procedure, use the SELECT statement with an INTO clause to retrieve a single row value from the database and assign it to a host variable. The SELECT statement must return at most one row from the database, like a standard singleton SELECT. The INTO clause is required and must be the last clause in the statement.
The INTO clause comes at the end of the SELECT statement to allow the use of UNION operators. UNION is not allowed in singleton SELECT statements in embedded SQL.
Example: The following statement is a standard singleton SELECT statement in an embedded application:
EXEC SQL
SELECT SUM(BUDGET), AVG(BUDGET)
INTO :TOT_BUDGET, :AVG_BUDGET
FROM DEPARTMENT
WHERE HEAD_DEPT = :HEAD_DEPT
To use the above SELECT statement in a procedure, move the INTO clause to the end as follows:
SELECT SUM(BUDGET), AVG(BUDGET)
FROM DEPARTMENT
WHERE HEAD_DEPT = :HEAD_DEPT
INTO :TOT_BUDGET, :AVG_BUDGET;
See Also
For a complete explanation of the standard SELECT syntax, see SELECT.