SELECT INTO
The SELECT INTO statement retrieves one row of results. The SELECT INTO statement is also known as a singleton SELECT statement.
Syntax
SELECT [select_list] INTO {:hvar [,...]} select_options
Arguments
select_list
Is the list of items (table columns or expressions) to retrieve data from.
hvar
Is one or more host variables to receive the select_list items.
select_options
Is one or more statements or other options that can be used with the Transact-SQL SELECT statement (for example, a FROM or WHERE clause). The GROUP BY, HAVING, COMPUTE, CUBE, and ROLLUP clauses are not supported.
Remarks
The SELECT INTO statement retrieves one row of results and assigns the values of the items in select_list to the host variables specified in the INTO list. If more columns are selected than the number of receiving host variables, then the value of SQLWARN3 is set to W. The data type and length of the host variable must be compatible with the value assigned to it. If data is truncated, the value of SQLWARN3 is set to W.
The Embedded SQL SELECT INTO statement is compatible with the Transact-SQL SELECT INTO statement. The Embedded SQL SELECT INTO statement is used only when results are retrieved for substitution in the application. The Transact-SQL SELECT INTO statement does not return results to the application and must be issued by using the Embedded SQL EXECUTE statement.
If more than one row is returned, SQLCODE is set to +1, which indicates an exception.
Examples
EXEC SQL SELECT au_lname INTO :name FROM authors WHERE stor_id=:id;