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.

Please refer to the Environment Setup page for a list of IP addresses to whitelist.

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:

Copy 
"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"            }
        ]
    ]
}