In this article

1 Overview
- 1.1 Snap Type
- 1.2 Prerequisites
- 1.3 Support for Ultra Pipelines
- 1.4 Limitations & Known Issues
2 Snap Views
3 Snap Settings
4 Examples
- 4.1 Downloads
5 Snap Pack History

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.

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 Failed to process result set). To fix the issue, add the following line to the stored procedure to turn off the output of the count: SET NOCOUNT ON

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

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

Input

Document

Min: 0
Max: 1

Execute Snap

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

Min: 2
Max: 2

Mapper Snap

This Snap has exactly two document output views, one regular output view, and one Result Set 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:

Stop Pipeline Execution: Stops the current pipeline execution if the Snap encounters an error.
Discard Error Data and Continue: Ignores the error, discards that record, and continues with the remaining records.
Route Error Data to Error View: Routes the error data to an error view without stopping the Snap execution.

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 whether 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 fieldset.

Field Name	Field Type	Description
Label* Default Value: SQL Server - Stored Procedure Example: 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 Example: schema_demo	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 Example: addSeventeen	String/Expression	Specify 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.
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 Example: Suppose there is a stored procedure named createGreeting. It has 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.	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.
Number of retries Default Value: 0 Example: 3	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 Example: 10	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 Example: Validate & Execute	Dropdown list

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:

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:

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:

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:

Downloads

	File	Modified
Labels No labels Preview View	File SQL Server Stored Procedure.slp	Feb 24, 2022 by Subhajit Sengupta

SnapLogic Documentation

SQL Server - Stored Procedure