ajDBRunSPToWorkbook function
Available since AlchemyJ v4.1.3
Description
The ajDBRunSPToWorkbook function executes a stored procedure and writes the result to a workbook. Please take note that to run this function from Excel, you would need to set up the Data Source Connection in ##ExternalResources.
Syntax
ajDBRunSPToWorkbook(target_workbook_path, start_address, 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 |
|---|---|---|
| target_workbook_path (required) | String | The path of the Excel file to be written. The function will create an Excel file automatically if the specified file path does not exist. Ensure that the current user has privilege to add/update files in the specified path. Remark: The function only supports the target workbook in xlsx and xlsm format and it cannot not be the same as the current workbook. |
| start_address (required) | String | The top-left cell of the range to write in the target workbook in the [sheet]!cell_address format. For example, "sheet1!A2". The function will check the range size in data_range to determine the actual range in the target workbook. |
| 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 va |
| 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:
1) Return Value: TRUE / #VALUE!
2) Return Type: Single Value
Example
ajDBRunSPToWorkbook is a combined function of ajDBRunStoredProc and ajWriteWorkbook.
We will use the following stored procedure as an example. The stored procedure name is SP_CUSTOMER and it returns 6 records.
We use the formula below to export the data to a specified path. The first parameter is the target workbook name. If the specified file does not exist, it will create a new workbook in the specified path. Otherwise, it will update the content to the existing one.
=ajDBRunSPToWorkbook(B32,B33,B34,,,,B38,,,,B42)
The start address is sheet1!B2, so the content starts from this cell. The column headers were also shown since the return header is TRUE.
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 |
|---|
| Target workbook path is not a xlsx file or a xlsm file. |
| Invalid start address, it should be in [sheet name]![cell address] format. For example, sheet1!A2. |
| Failed to write workbook. |
| Target workbook is current workbook. |
| DB connection error. |
| 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 |