Using Stored Procedures (Embedded SQL Guide)

Go Up to Working with Stored Procedures

There are two types of procedures that can be called from an application:

• Select procedures that an application can use in place of a table or view in a SELECT statement. A select procedure must return one or more values, or an error results.
• Executable procedures that an application can call directly, with the ­EXECUTE PROCEDURE statement. An executable procedure may or may not return values to the calling program.

Both kinds of procedures are defined with CREATE PROCEDURE and have the same syntax. The difference is in how the procedure is written and how it is intended to be used. Select procedures always return zero or more rows, so that to the calling program they appear as a table or view. Executable procedures are simply routines invoked by the calling program that can return only a single set of values.

In fact, a single procedure conceivably can be used as a select procedure or an executable procedure, but this is not recommended. In general a procedure is written specifically to be used in a SELECT statement (a select procedure) or to be used in an EXECUTE PROCEDURE statement (an executable procedure). For more information on creating stored procedures, see the Data Definition Guide.

Topics

• Procedures and Transactions
• Security for Procedures