InterBase Quick Start: Part V - Stored Procedures

From InterBase

Go Up to InterBase Quick Start: Part V - Advanced Topics

Stored procedures are programs stored within the metadata of a database. Applications (including IBConsole) can call stored procedures to perform tasks. See Working with Stored Procedures for more information about stored procedures.

There are two types of stored procedures:

  • Select procedures: An application can use a select procedure in place of a table or view in a SELECT statement. A select procedure must be defined to return one or more values (output parameters), or an error result. Since select procedures can return more than one row, they appear as a table or view to a calling program.
  • Executable procedures: An application can call an executable procedure directly with the EXECUTE PROCEDURE statement. Executable procedures can perform a variety of tasks and they can return values to the calling program.

The CREATE PROCEDURE syntax applies to both tpes of procedures. The difference is in how you write the procedure and how you intend to use it.

Stored Procedure Syntax

A CREATE PROCEDURE statement has a header and a body.

The header contains:

  • The name of the stored procedure, which must be unique among procedure, view, and table names in the database.
  • An optional list of input parameters and their data types that a procedure receives from the calling program.
  • If the procedure returns values to the calling program: the RETURNS keyword, followed by a list of output parameters and their data types.

The body contains:

  • An optional list of local variables and their data types.
  • A block of statements in InterBase procedure and trigger language, enclosed with BEGIN and END keywords. You are allowed to nest blocks. The simplified syntax of a procedure is:
CREATE PROCEDURE procedure_name [(input_var1 data TYPE[,input_var2 data TYPE ])] 
[returns (output_var1 data TYPE[, output_var2 data TYPE ])] AS
BEGIN
 /* 
  * statements in InterBase
  * procedure and trigger language
  */
END

Like with trigger definitions, you must precede procedure definitions in SQL scripts, embedded SQL, and command-line isql by a SET TERM statement that sets the terminator to something other than a semicolon. At the end of the procedure code, you must use the SET TERM statement again to set the terminator back to a semicolon.

Advance To: