ajDBRunStoredProc function
Description
The ajDBRunStoredProc function executes a stored procedure and returns the result. This function can support MySQl, MSSQL and Oracle database.
Syntax
ajDBRunStoredProc(stored_procedure_name, [parameter], [return_type], [transpose], [return_header], [data_source_id], [convert_to_text], [run_condition], [run_by_function_point_only])
| Argument Name | Argument Type | Description |
|---|---|---|
| stored_procedure_name (required) | Range / Array | The name of the stored procedure to execute. |
| parameter (optional) | Double | The range of cells to form the list of input and output parameters. Refer to the SQLParameters Snippet to see how to use this snippet to define the required fields. |
| return_type (optional) | Double | 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. The default value is 0. |
| transpose (optional) | Boolean | If it equals TRUE, the return result will be transposed. This only affects the return that has multiple cells. The default value is FALSE. |
| return_header (optional) | Boolean | If it equals TRUE, the return result will include the column headers as the first row. This only applies when ‘return_type’ is 0 or 1. If it equals FALSE, the return result will contain data only. The default value is FALSE. |
| data_source_id (optional) | String | The data source shall be used in this database operation. It shall be defined in ##ExternalResources worksheet. The default value is "primary". |
| convert_to_text (optional) | Boolean | 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. The default value is FALSE. |
| run_condition (optional) | Boolean | The function will run when the value is TRUE. Otherwise, it will not run. The default value is TRUE. |
| run_by_function_point_only (optional) | Boolean | If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE. |
The function will return:
- Return Value: Database operations results or the count
- Return Type: Single Value / Multiple values (array formula)
Before using ajDBRunStoredProc, the database connection must be set up in the Data Source Connection in ##ExternalResources.
Example
This example uses the following stored procedure. The output result is the sum of parameter 1 and parameter 2.
Click AlchemyJ ribbon Insert Snippet and select SQL Parameters . A table will be inserted. In this example, it sets p1 as 20, p2 as 10 and set the p3 as output parameter. Input the range of SQL parameters (B3:E6) as the second parameter. The return_type is 1 since it returns the output parameter.
Please note that the parameter sequence must match the parameter sequence of stored procedure. If the stored procedure contains some optional parameters, use an empty input for it. For example, p2 is an optional parameter, then the parameter p2 should be kept.
Click here to download the use case workbooks for further reference.
Error Scenarios
It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).
| Error Scenario |
|---|
| Invalid stored procedure name. |
| Invalid return type, it must be 0, 1, or 2. |
| Return header is TRUE, but return type is not 0 or 1. |
| For PostgreSQL, the return type is 0 or 2 |
| DB connection error. |