ajExecuteSp function
Description
The ajExecuteSp function executes a stored procedure and returns the result.
Syntax
ajExecuteSp(stored_procedure_name, [parameter], [return_type], [transpose], [return_header], [data_source_id], [convert_to_text], [run_condition], [run_by_function_point_only])
| Argument Name | Description |
|---|---|
| stored_procedure_name (required) | Specify the name of the stored procedure to execute. |
| parameter (optional) | The range of cells to form the list of input and output parameters to be specified in the following format. The range of cells can span across multiple rows and columns. The parameter sequence needs to be matched with the sequence of stored procedure. <field_name>-##-<value>-##-<data_type>-##-<in_out_flag>-||- <field_name>-##--<value>-##-<data_type>-##-<in_out_flag>-||-... Where, -||- denotes AlchemyJ parameter separator. -##- denotes AlchemyJ parameter type separator. For example, RECORD_ID1-##-21-##-NUMBER-##-IN-||-RECORD_ID2-##-23-##- NUMBER-##-IN-||-OUTPUT -##--##- NUMBER -##- OUT Please take note that both separator symbols can be configured in %%AppConfig Worksheet. If you do not specify anything, the default value will always be an empty string (“”). |
| return_type (optional) | 0 indicates the function will return a set of records (in cell array). 1 indicates the function will return the output parameters. 2 indicates to return the number of the affected records, only works for stored procedure with SQL select statement. If you do not specify anything, the default value will always be 0. |
| transpose (optional) | If it equals TRUE, the return result will be transposed. This only affects the return that has multiple cells. If you do not specify anything, the default value will always be FALSE. |
| return_header (optional) | If it equals TRUE, the return result will include the column headers as the first row. If it equals FALSE, the return result will contain data only. This only applies when ‘return_type’ is 0. If you do not specify anything, the default value will always be FALSE. |
| data_source_id (optional) | Specify the data source ID. The parameter is used to define which database should be executed for the function. If you do not specify anything, the default value will always be 'primary'. |
| convert_to_text (optional) | If it equals TRUE, the return result will be converted to string values. If it equals FALSE, the return result will preserve the original data type but any string value that has more than 256 characters will cause an error. If you do not specify anything, the default value will always be FALSE. |
| run_condition (optional) | The function will run when the value is TRUE. Otherwise, it will not run. If you do not specify anything, the default value will always be TRUE. |
| run_by_function_point_only (optional) | If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Run Function Point. If it equals TRUE, the function can be executed with Run Function Point (AlchemyJ toolbar \ Run Function Point) only. If you do not specify anything, the default value will always be TRUE. |
The function will return:
- Content type: Database operations results or the count
- Method: Within a cell / cell array
Before using ajExecuteSP, the database connection must be set up from the Properties dialog box.
Example
This example uses the following stored procedure. The output result is the sum of parameter 1 and parameter 2.
In this example, it sets p1 as 20 and p2 as 10. Then parameter was set as 'p1-##-20-##-number-##-IN-||-p2-##-10-##-number-##-IN-||-p3-##--##-number-##-OUT' and the return_type is 1 since it returns the output parameter.
Please note that the parameter sequence needs to be matched with the sequence of stored procedure. When there has some optional paramters no need to be inputted, then use empty input for the optional parameter if it is in the middle. For example, p2 is an optional parameter, then the parameter should be set as 'p1-##-20-##-number-##-IN-||-p2-||-p3-##--##-number-##-OUT'.