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
SELECTstatement. A select procedure must return one or more values, or an error results. - Executable procedures that an application can call directly, with the
EXECUTEPROCEDUREstatement. 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.
Procedures and Transactions
Procedures operate within the context of a transaction in the program that uses them. If procedures are used in a transaction, and the transaction is rolled back, then any actions performed by the procedures are also rolled back. Similarly, a procedure’s actions are not final until its controlling transaction is committed.
Security for Procedures
When an application calls a stored procedure, the person running the application must have EXECUTE privilege on the stored procedure. An extension to the GRANT statement enables assignment of EXECUTE privilege, and an extension to the REVOKE statement enables removal of the privilege. For more information about granting privileges to users, see the Data Definition Guide.
In addition, if the stored procedure accesses objects in the database, one of two things must be true: either the user running the application or the called stored procedure must have the appropriate permissions on the accessed objects. The GRANT statement assigns privileges to procedures, and REVOKE eliminates privileges.