Using SELECT Statements

Go Up to The Procedure Body

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.

For example, the following statement is a standard singleton SELECT statement in an application:

EXEC SQL
SELECT SUM(BUDGET), AVG(BUDGET)
INTO :tot_budget, :avg_budget
FROM DEPARTMENT
WHERE HEAD_DEPT = :head_dept;

To use this 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;

For a complete discussion of SELECT statement syntax, see the Language Reference.