The call sproc statement executes the named stored procedure on the data source.
call sproc sproc_name, ret_val {, parameter, parameter, ...}
• sproc_name – A string containing the name of the stored procedure you want to run.
• ret_val –A long integer variable containing the error code returned by the stored procedure. By default, SQL native error codes are returned. The value 0 corresponds to no error, while the values -1 to -14 represent native SQL server error codes. You can choose to have the stored procedure return another value through the ret_val parameter, but this will override any native error value returned.
• parameter – Values, such as strings or integers, that you want to pass to or have returned from the stored procedure.
Stored procedures are precompiled SQL statements stored in a database. Using stored procedures can improve your application’s performance, since all of the processing takes place at the server. Stored procedures can call other stored procedures.
The call sproc statement doesn’t verify the existence of the named stored procedure or the items in the parameter list when the script containing it is compiled. If the procedure is unsuccessful when run in your application, the return value will be -127, the value corresponding to the SQL_SPROC_ERROR constant.
|
The Dexterity long integer control type corresponds to the SQL integer data type. Therefore, your stored procedure’s return value must be declared as an integer, but the Dexterity ret_val to which it is returned must be a long integer. Stored procedures must not generate a result set. Only returned values and parameters can be stored procedure outputs. |
The Dexterity dictionary must contain a prototype procedure for the named stored procedure. This prototype procedure must have the same name as the stored procedure. The first non-comment line of the prototype procedure indicates what the stored procedure will return. It has the following format:
sproc returns long variable;
Then any additional in, out or inout parameters for the stored procedure are declared. This prototype reserves the necessary space in the client’s memory for any out parameters. The call sproc statement must be used in the prototype procedure to make the actual call to the SQL stored procedure. When you call the prototype procedure, the SQLScriptPath procedure will be called implicitly, which adds path information to the stored procedure named in the call sproc statement.