Retrieving Single Rows

---

If the result of a SELECT statement is known to be a single row, the SELECT INTO statement may be used as an alternative to fetching data through a cursor.

This is a much simpler programming construction, since cursors are not used and the only requirement is that host variables used in the SELECT INTO statement are declared in the DECLARE SECTION.

However, there are two disadvantages associated with SELECT INTO:

• An error occurs if the result set addressed by the search condition contains more than one row. In other words, SELECT INTO can only be reliably used when there is no possibility of a multi-row result set (essentially when the search condition includes the columns that form a UNIQUE or PRIMARY KEY column or returns just the result of a set function, e.g. COUNT(*)).
• Execution of the SELECT INTO statement involves a check that the result set contains one single row, which may incur unnecessary overhead. Even if it is known that the result row is unique, a single FETCH operation through a cursor may be a more efficient implementation.

Use of a SELECT INTO statement is justified when the result set may contain several rows, but it is a condition for continued execution of the application program that the result row is unique. With a cursor, this would require a construction that checked that one and only one FETCH operation could be performed (alternatively, use a separate SELECT COUNT with the same search condition as the cursor). In such a case, a SELECT INTO statement with a check on the return code, see Handling Errors and Exceptions, is probably the preferred solution.

A CALL statement can be used to return information to the one or more host variables associated with the output parameter(s) of the procedure.

A SET statement can be used with a function invocation to return information to one host variable.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se