Azure SQL - Stored Procedure

Overview

The Azure SQL - Stored Procedure Snap invokes a stored procedure in the Azure SQL Database.

If the procedure contains any OUT parameters, they are written to the output view.

This is a Write-type Snap.
Works in Ultra Tasks

Supported Accounts

Prerequisites

The stored procedure to be invoked must be created before executing the Snap.
A valid account with the required permissions.

Limitations

This Snap can invoke only stored procedures but not functions. For functions, use the Azure SQL - Execute Snap.

Snap views


Type	Description	Examples of upstream and downstream Snaps
Input	This Snap allows up to one input view. If an input view is defined, then values from an incoming document can be used as input parameters to the stored procedure.	Mapper JSON Generator Structure
Output	This Snap has exactly two document output views: one regular output view and one Result Set output view. The Results Set output view contains the result set of the stored procedure. The Output Parameters output view contains a key-value pair for each output parameter. It will be an empty map document if there is no output parameter.	Mapper
Learn more about Error handling.

Examples

Basic Use Case (Azure SQL - Stored Procedure): This example demonstrates how to invoke a stored procedure in an Azure SQL database, pass an input parameter from an upstream Snap, and retrieve results in the result set output view.

Snap settings

Note: Learn about the common controls in the Snap settings dialog.


Field/Field set	Description
Label `String`	Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if more than one of the same Snaps is in the pipeline. Default value: Azure SQL - Stored Procedure
Schema Name `String/Expression/ Suggestion`	The schema name where the procedure resides. The property is suggestible and will return all schemas of the DB. Note: The values can be passed using the pipeline parameters but not the upstream parameter. Default value: N/A Example: dbo
Stored Procedure Name `String/Expression/ Suggestion`	Required. The stored procedure to execute. The property is suggestible and will return all procedures of the DB. 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 it will return all procedures of that schema. Default value: N/A Example: addSeventeen
Parameter Values `String/Expression`	The input parameter values for the stored procedure. If you define parameter values, you must do so in the same order as they appear in the stored procedure definition. Note: Quotation marks should not be used when setting input parameters unless they are part of the input value itself, since they are treated as escaped plain characters. For example, if the desired input value is TEST_VALUE1, the input parameter should be set as TEST_VALUE1. If you set it as "TEST_VALUE1", it will be recognized as \"TEST_VALUE1\". Tip: If you do not provide any parameters in this Snap, you can still view the required input parameters in the Target Schema of the upstream Mapper Snap. The parameters are dynamically populated based on the column keys of the stored procedure; you can map the parameters and pass them as an input to the Stored Procedure Snap. Default value: N/A Example: Suppose there is a stored procedure named createGreeting with three parameters p1, p2, and p3. p1 and p2 are input parameters of type VARCHAR2. Then there have to be two string values provided. The first value goes to p1 and the second to p2.
Snap execution `Dropdown list`	Choose one of the three modes in which the Snap executes. Available options are: `Validate & Execute`. Performs limited execution of the Snap and generates a data preview during pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during pipeline runtime. `Execute only`. Performs full execution of the Snap during pipeline execution without generating preview data. `Disabled`. Disables the Snap and all Snaps that are downstream from it.

Troubleshooting


Error	Cause	Resolution
`Invalid object name`	The input provided (Schema name or SP name) is incorrect.	Ensure the input provided (Schema name and SP name) is correct.
`Procedure expects parameter which was not supplied`	The input parameters are not provided correctly.	Ensure the input parameters are provided correctly and in the same order as they appear in the stored procedure definition.