Sybase 12.4.2 manual Returning results as procedure parameters, 247

CHAPTER 6 Using Procedures and Batches

CREATE VARIABLE returnval INTEGER ; returnval = CALL myproc() ;

Returning results as procedure parameters

Procedures can return results to the calling environment in the parameters to the procedure.

Within a procedure, parameters and variables can be assigned values in one of the following ways:

Using the SET statement

•The parameter can be assigned a value using the SET statement.

•The parameter can be assigned a value using a SELECT statement with an INTO clause.

The following somewhat artificial procedure returns a value in an OUT parameter that is assigned using a SET statement:

CREATE PROCEDURE greater (IN a INT,

IN b INT,

OUT c INT)

BEGIN

IF a > b THEN

SET c = a;

ELSE

SET c = b;

END IF ;

END

Using single-row SELECT statements

Note The preceding example is artificial: generally a function is easier to use than a procedure when only one result is required.

Single-row queries retrieve at most one row from the database. This type of query is achieved by a SELECT statement with an INTO clause. The INTO clause follows the select list and precedes the FROM clause. It contains a list of variables to receive the value for each select list item. There must be the same number of variables as there are select list items.

247