Executing Stored Procedures

Go Up to Developing InterClient Programs

A stored procedure is a self-contained set of extended SQL statements that are stored in a database as part of its metadata. Stored procedures can pass parameters to and receive return values from applications. From the application, you can invoke a stored procedure directly to perform a task, or you can substitute the stored procedure for a table or view in a SELECT statement. There are two types of stored procedures:

• Select procedures are used in place of a table or view in a SELECT statement. A selectable procedure generally has no IN parameters. See note below.
• Executable procedures can be called directly from an application with the EXECUTE PROCEDURE statement; they may or may not return values to the calling program.

Use the Statement class to call select or executable procedures that have no SQL input (IN) parameters. Use the PreparedStatement class to call select or executable stored procedures that have IN parameters.

Note: Although it is not commonly done, it is possible to use IN parameters in a SELECT statement. For example:

create procedure with_in_params(in_var integer)
returns (out_data varchar(10))
as
begin
  for select a_field1 from a_table
  where a_field2 = :in_var
  into :out_data
  do suspend;
end

To return one row:

execute procedure with_in_params(1)

To return more than one row:

select * from with_in_params(1)

Topics

• Statement Example
• PreparedStatement Example