Stored procedures are compiled blocks of code in the relational database. They are useful since they can have conditional statements and flow statements. Stored procedures can perform INSERT, UPDATE, DELETE, and SELECT commands. However, stored procedures can also take arguments and return results. The arguments may be values to insert or values to use in where clauses.
Procedures can be much faster than SQL commands for the following reasons:
- Executing a stored procedure requires only one call.
To execute an SQL command, two calls are made to the relational database: one to retrieve the command and another to execute it. Using a stored procedure, the reference is made by name and the name is passed to the database, including any parameters.
- A stored procedure is already compiled in the database.
When using an SQL command that is not stored, both calls are ad-hoc queries, so the database must compile them at runtime (unless caching is used). Refer to the Using Command Caching section for more information on configuring the SQL task to use command caching.
To configure the SQL blocks to use a stored procedure, use the following information:
- Select Procedure in the Command Type group box in the SQT block's SQL Parameters dialog box.
- Enter the name of the procedure in the SQL Name field, using no more than eight characters.
- Define any input arguments required by the stored procedure using the Direction field in the SQD block.
For example, if the stored procedure takes two input arguments and returns data from a SELECT statement, configure the SQD block with OUT tags for the parameter markers and IN tags for the results from the SELECT statement.
Refer to the Selecting Multiple Rows section for more information on configuring the database blocks to use multiple rows of data.
NOTE: Microsoft Access does not support the use of stored procedures. If you are using Microsoft Access as your relational database, do not use the PROCEDURE command in your command language scripts and do not select the Stored Procedures option button in the SQL Command Configurator. Doing so can produce unexpected results.
