Stored procedures are compiled blocks of code in the relational database. They are useful because they can have conditional statements and flow statements. Stored procedures can perform INSERT, UPDATE, DELETE, and SELECT commands. They 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.
- A stored procedure is already compiled in the database.
- A stored procedure runs on the server, not on the client.
When VisiconX uses a stored procedure to access and retrieve data, it creates a list of query properties that map to the parameters of the stored procedure. The query property list displays the query property / parameter mapping, the parameter's data type, and the direction the parameter passes information, as the following illustration shows.
Stored Procedure Parameter Mapping
VisiconX assigns query properties to the return value, input parameters, output parameters, and input/output parameters defined to the stored procedure. In cases where VisiconX cannot determine the purpose of a parameter, the direction in the Parameter List appears as Unknown.
NOTES:
- If you use a stored procedure to query a Microsoft Access database or Oracle database, please see Troubleshooting Problems Caused By Stored Procedures for special considerations that apply to these databases.
- CIMPLICITY users need to use CimEdit Basic script to use query properties. For more information, see Using Query Properties in CIMPLICITY.
- The Datetime output parameter does not return milliseconds when executed through a stored procedure. (The ADOParameter returned to the VisiconX object does not contain the millisecond part when a stored procedure returns a datatype of datetime.)
After viewing the Parameter List for the stored procedure, make a note of the query properties that map to the input parameters for the stored procedure. These are the query properties that must be animated in order to retrieve data using the stored procedure.
