CHAPTER 6 Using Procedures and Batches

Passing parameters to procedures

You can take advantage of default values of stored procedure parameters with either of two forms of the CALL statement.

If the optional parameters are at the end of the argument list in the CREATE PROCEDURE statement, they may be omitted from the CALL statement. As an example, consider a procedure with three INOUT parameters:

CREATE PROCEDURE SampleProc( INOUT var1 INT DEFAULT 1,

INOUT var2 int DEFAULT 2, INOUT var3 int DEFAULT 3 )

...

We assume that the calling environment has set up three variables to hold the values passed to the procedure:

CREATE VARIABLE V1 INT;

CREATE VARIABLE V2 INT;

CREATE VARIABLE V3 INT;

The procedure SampleProc may be called supplying only the first parameter as follows:

CALL SampleProc( V1 )

in which case the default values are used for var2 and var3.

A more flexible method of calling procedures with optional arguments is to pass the parameters by name. The SampleProc procedure may be called as follows:

CALL SampleProc( var1 = V1, var3 = V3 )

or as follows:

CALL SampleProc( var3 = V3, var1 = V1 )

Passing parameters to functions

User-defined functions are not invoked with the CALL statement, but are used in the same manner that built-in functions are. For example, the following statement uses the fullname function defined in “Creating user-defined functions” to retrieve the names of all employees:

SELECT fullname(emp_fname, emp_lname) AS Name

FROM employee

245

Page 265
Image 265
Sybase 12.4.2 manual Passing parameters to procedures, Passing parameters to functions, Or as follows, 245