Calling Stored Procedures
Applications can call stored procedures from SQL and DSQL. You can also use stored procedures in isql. For more information on calling stored procedures from applications, see the Embedded SQL Guide.
There are two types of stored procedures:
- SELECT procedures that an application can use in place of a table or view in a
SELECTstatement. A select procedure must be defined to return one or more values (output parameters), or an error results.
- Executable procedures that an application can call directly with the
EXECUTE PROCEDUREstatement. An executable procedure can optionally return values to the calling program.
Both kinds of procedures are defined with
CREATE PROCEDURE and have essentially the same syntax. The difference is in how the procedure is written and how it is intended to be used. Select procedures can return more than one row, so that to the calling program they appear as a table or view. Executable procedures are routines invoked by the calling program, which can optionally return values.
In fact, a single procedure conceivably can be used as a select procedure or as an executable procedure, but 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).