SQL Stored Procedure
The SQL Stored Procedure action allows you to invoke a stored procedure on a remote Microsoft SQL Server or Azure SQL instance.
| Input | Output |
|---|---|
| Any (`JSON`) | Any (`JSON`) (*Passthrough*) and Response Data (`object`) (*Optional*) |
Compatibility
- Microsoft SQL Server 2016 and newer
- Azure SQL
Features and limitations
| Feature | Limitations? |
|---|---|
| SQL Login Authentication | No |
| Windows or Microsoft Entra ID Authentication | Yes |
| Execute standard and encrypted stored procedures | No |
| Pass parameters | No |
| Pass or retrieve `OUTPUT` parameters | Yes |
| Pass table-valued parameters | Yes |
| Read log output (via `RAISERROR` or similar) | Yes |
| Read result sets (includes support for multiple result sets) | No |
| Read the return code emitted by the `RETURN` statement | No |
Networking and firewall considerations
In order for the stored procedure to be called, the iWorkflow engine environment needs to be able to connect to the target SQL Server.
See Whitelisting the IP for Power Suite products for a list of IPs.
Properties
| Name | Type | Templatable | Notes |
|---|---|---|---|
| Host | Text | No | The hostname or IP address of the SQL Server. If using a DNS name, the name must be able to be resolved publicly. |
| Port | Number | No | The SQL server port number. Defaults to the standard SQL port of 1433. Do not modify unless you are certain that your SQL server runs on a non-standard port. |
| Database | Text | No | The name of the database (or “Initial Catalog”) to connect to. |
| Username | Text | No | The username to authenticate with. |
| Password | Text | No | The password to authenticate with. |
| Procedure Name | Text | No | The name of the stored procedure. Do not include `EXEC` or any parameters. |
| Parameters | List | Yes | Specify the parameters to pass to the stored procedure. Ensure the parameter names match the procedure definition exactly, including casing. All required parameters must be specified or the action will fail. |
| Timeout (minutes) | Number | No | Specify the number of minutes to wait for the procedure to execute, between 1 and 60. Defaults to 5 minutes. |
| Use Transaction Coming Soon | Checkbox | No | Specifies whether or not to wrap the procedure execution inside a transaction. |
| Output Property | Text | No | Specify the name of the property to store the output data of the stored procedure. The output schema is as follows, if for example the output property is named `mySp`: “mySp”: { “returnValue”: 0, “data”: [ [ { “Column1”: “Value 1”, “Column2”: “Value 2” }, { “Column1”: “Value 3”, “Column2”: “Value 4” } ], [ { “T2Column1”: “Table 2 / Value 1”, “T2Column2”: “Table 2 / Value 2” }, { “T2Column1”: “Table 2 / Value 3”, “T2Column2”: “Table 2 / Value 4” } ] ] } |