Azure Synapse SQL - Stored Procedure
In this article
Overview
You can use this Snap to execute stored procedures in the Azure Synapse SQL Database.
Snap Type
The Azure Synapse SQL - Stored Procedure Snap is a WRITE-type Snap.
Prerequisites
Valid Azure Synapse SQL Account.
Access to the Azure Synapse database and the required permissions to write.
Support for Ultra Pipelines Â
Works in Ultra Pipelines.Â
Limitations
This Snap depends on metadata for executing Stored Procedures. There is a JDBC limitation to access System stored procedure metadata. Therefore, you cannot use this Snap to execute the system Stored Procedures.
This Snap does not display any error when you use invalid Stored Procedures. Therefore, we recommend that you use the Suggestions list to populate the available stored procedures list from the server.
Known Issues
None.
Snap Views
Type | Format | Number of Views | Examples of Upstream and Downstream Snaps | Description |
---|---|---|---|---|
Input | Document  |
|
| If an input view is defined, then values from an incoming document can be used as input parameters to the stored procedure. |
Output | Document  |
|
| If the procedure contains any OUT parameters, they are written to the output view. |
Error | Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter when running the Pipeline by choosing one of the following options from the When errors occur list under the Views tab:
Learn more about Error handling in Pipelines. |
Snap Settings
Asterisk ( * ): Indicates a mandatory field.
Suggestion icon (): Indicates a list that is dynamically populated based on the configuration.
Expression icon ( ): Indicates the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.
Add icon ( ): Indicates that you can add fields in the field set.
Remove icon ( ): Indicates that you can remove fields from the field set.
Field Name | Field Type | Description | |
---|---|---|---|
Label* Default Value:Â Azure Synapse SQL - Stored Procedure | String | Specify a unique name for the Snap. Â | |
Schema Name Default Value:Â N/A | String/Expression/Suggestion | Specify the schema name of the database where the Stored Procedure resides. | |
Package Name Default Value:Â N/A | String/Expression/Suggestion | Specify the package name that contains the Stored Procedures. | |
Stored Procedure Name Default Value: N/A | String/Expression/Suggestion | Specify the Stored Procedure name to execute. If a package is defined, then it will only return the procedures of that package. If a schema is defined and no package is defined, then the Snap returns all procedures of that schema. | |
Parameter Values | Use this field set to specify advanced parameters that you want to include in the request. | ||
Parameter Value Default Value: None | String/Expression | Select the input parameter values for the stored procedure. The parameter values are bound to the input columns in the order they appear in the list. Therefore, you must define the values in the same order as they appear in the Stored Procedure definition. Do not use quotation marks when setting input parameters unless they are part of the input value itself, because they are treated as escaped plain characters. For example, if the desired input value is TEST_VALUE1, set the input parameter as TEST_VALUE1, if you set it as "TEST_VALUE1", it is recognized as \"TEST_VALUE1\". | |
Snap Execution Default Value:Â | Dropdown list | Select one of the three modes in which the Snap executes. Available options are:
|
Example
Calling a Stored Procedure
This example Pipeline demonstrates how to call a simple stored procedure.
Step 1: Configure the Azure Synapse SQL - Stored Procedure Snap to read the employee data from SelectAllEmployee Stored Procedure:
On validation, the list of employee details is generated in the second output view:
Step 2: Connect the JSON Formatter Snap to the upstream Stored Procedure Snap to read the output document.
Step 3: Connect the File Writer Snap to the JSON Formatter Snap to write the employee.json to the SnapLogic database.
Â
Downloads
Download and import the Pipeline into SnapLogic.
Configure Snap accounts as applicable.
Provide Pipeline parameters as applicable.
Â