DataDef.pdf, CHAPTER 9 WORKING WITH STORED PROCEDURES INTERBASE 6
Using assignment statements
---------------------------
A procedure can assign values to variables with the syntax:
variable = expression;
where expression is any valid combination of variables, operators, and expressions, and
can include user-defined functions (UDFs) and generators.
A colon need not precede the variable name in an assignment statement. For example,
the following statement assigns a value of zero to the local variable, ANY_SALES:
any_sales = 0;
Variables should be assigned values of the datatype that they are declared to be. Numeric
variables should be assigned numeric values, and character variables assigned character
values. InterBase provides automatic type conversion. For example, a character variable
can be assigned a numeric value, and the numeric value is automatically converted to a
string. For more information on type conversion, see the Embedded
SQL Guide.
Using SELECT statements
-----------------------
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.