...
...
...
...
...
...
...
...
...
In this
...
article
| Table of Contents | ||||
|---|---|---|---|---|
|
...
Snap type:
...
Write
...
Description:
...
Overview
You can use this Snap to execute a stored procedure in a SQL Server database and writes the values
...
of OUT parameters to the output view.
Expected upstream Snaps: Any Snap with document output view
Expected downstream Snaps: Any Snap with document input view
Expected input: Input document may contain values for the Snap expression properties, typically for the Parameter values property.
Example: "John" and "Smith" are values for the stored procedure's input parameters, the first and the second, respectively.
| Code Block |
|---|
{
firstName: "John". lastName: "Smith" } |
Expected output: Output document in the output view contains a key-value pair for each output parameter. It will be an empty map document if there is no output parameter. Output document in the Result Set output view contains the result set of the stored procedure.
...
None
...
- Ultra Pipelines: Works in Ultra Pipelines.
...
This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. See Configuring SQL Server Accounts for information on setting up this type of account.
...
| Info |
|---|
Starting from SQL Server 2008, the result set from a Transact-SQL statement or stored procedure includes the count of the number of rows affected. This Snap fails to parse this extra information and may display an error (such as |
...
Snap Type
SQL Server - Stored Procedure is a Write-type Snap that executes an SQL Server stored procedure.
Prerequisites
None.
Support for Ultra Pipelines
Works in Ultra pipelines.
Limitations & Known Issues
None.
Snap Views
Type | Format | Number of Views | Examples of Upstream and Downstream Snaps | Description |
|---|---|---|---|---|
Input | Document |
|
| 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. |
Output | Document |
|
| This Snap has exactly two document output views, one regular output view, and one Result Set output view. |
Error |
...
This Snap has at most one error view and produces zero or more documents in the view.
...
Settings
Label
...
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
| Info |
|---|
|
Field Name | Field Type | Description |
|---|---|---|
Label*
Default Value: SQL Server - Stored Procedure | String | Specify the name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your |
...
Pipeline. |
Schema Name |
|---|
...
Default Value: N/A | String/Expression | Specify the schema name where the procedure resides. The property is suggestible and will return all schemas of the DB. |
|---|
...
The values can be passed using the pipeline parameters but not the upstream parameter. |
Stored Procedure Name* Default Value: N/A |
|---|
...
addSeventeen | String/Expression | Specify the Stored Procedure |
|---|
...
Parameter Values
...
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. |
Example: addSeventeen
Default value: [None]
Parameter Values | Use this fieldset to set Parameter Values to be used in the stored procedure. | |
|---|---|---|
...
The parameter values will be bound to input columns in the order they appear in the list. | |
Parameter Value Default Value: N/A | |
|---|---|
...
String/Expression | Specify 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.
|
...
|
...
|
...
...
|
...
| ||
Pass through Default Value: Deselected | Checkbox | If selected, the input document will be passed through to the output views under the key 'original'. The document will be passed to the Parameter output view, and it will also be passed to the Result Set output view if there is output sent from this view. |
|---|
...
This property applies only to the Execute Snaps |
...
with SELECT statement or the Select Snaps. | ||
Number of retries
Default Value: 0 | Integer/Expression | Specify the maximum number of attempts to be made to receive a response. The request is terminated if the attempts do not result in a response.
|
|---|---|---|
Retry interval (seconds) |
...
Default Value: 1 | Integer/Expression | Specify the time interval between two successive retry requests. A retry happens only when the previous attempt resulted in an exception. | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Default Value: Execute only | Dropdown list |
|
...
| Info |
|---|
The Snap does not support the custom type parameter since SQL Server JDBC driver lacks support for this feature. |
...
http://blogs.msdn.com/b/jdbcteam/archive/2012/04/03/how-would-you-use-table-valued-parameters-tvp.aspx |
Examples
The following example is an application of the SQL Server Stored Procedure Snap for the example found at http://database.ittoolbox.com/groups/technical-functional/sql-server-l/example-of-code-that-overloads-a-sql-server-stored-procedure-5406272
Create a table called dbo.EMP by executing the following code:
| Code Block |
|---|
CREATE TABLE [dbo].[EMP]( [EMPNO] [int] NOT NULL, [ENAME] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DEPTNO] [int] NOT NULL, CONSTRAINT [EMP_EMPNO_PK] PRIMARY KEY CLUSTERED ( [EMPNO] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] |
Insert several rows:
| Code Block |
|---|
INSERT INTO [dbo].[EMP] VALUES (1111,’EDWARDS’,10);
INSERT INTO [dbo].[EMP] VALUES (2222,’ALLEN’,10);
INSERT INTO [dbo].[EMP] VALUES (3333,’WATTS’,20);
INSERT INTO [dbo].[EMP] VALUES (4444,’KIRAN’,20);
INSERT INTO [dbo].[EMP] VALUES (5555,’CASTER’,20);
INSERT INTO [dbo].[EMP] VALUES (6666,’VIJI’,30);
INSERT INTO [dbo].[EMP] VALUES (7777,’DATTA’,30); |
Create a store procedure "EMP_PROC" with three input parameters:
| Code Block |
|---|
CREATE proc [dbo].[EMP_PROC] @empno int=null, @ename varchar(10)=null, @deptno int=null AS IF EXISTS(SELECT 1 from [EMP] where EMPNO = @empno) BEGIN UPDATE [EMP] SET [EMPNO] = COALESCE(@empno,EMPNO), [ENAME] = COALESCE(@ename,ENAME), [DEPTNO] = COALESCE(@deptno,DEPTNO) WHERE EMPNO = @empno END |
Build and execute the pipeline:
The input data to the SQL Server - Stored Procedure Snap is:
| Code Block |
|---|
{
"EMPNO" : 1111,
"ENAME" : "Ted",
"DEPTNO" : 20 } |
...
| Info |
|---|
Only two input parameter values are provided while there are three input parameters in the procedure. This is possible because the procedure is written to support the overload feature. Executing the above pipeline will result in the update of ENAME value in the first row from "EDWARD" to "Ted". |
Downloads
| Multiexcerpt include macro | ||||
|---|---|---|---|---|
|
| Attachments | ||
|---|---|---|
|
Snap Pack History
| Expand | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
|
...