Versions Compared

Old Version 22

changes.mady.by.user Subhajit Sengupta

Saved on

compared with

New Version 23

changes.mady.by.user Subhajit Sengupta

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

On this Page

Table of Contents
maxLevel2
excludeOlder Versions|Additional Resources|Related Links|Related Information

Snap type:

Write

Description:

This Snap executes 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.


Prerequisites:

None


Support and limitations:
Account: 

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.


Views:


InputThis 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.
OutputThis 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


Required. 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


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.

Example: dbo


Stored Procedure Name

Required. 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.

Example: addSeventeen
Default value: [None] 


Parameter Values


Select 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.

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.

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\".

Default value: None.


Info

If you do not provide any parameters in the 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.


Note
titleBypassing Parameters

If you wish to bypass any parameter then add a blank row in its place in this section. This is required since the Snap expects the parameters in the order in which they are defined.

For example, if there are three parameters and they are defined as Pipeline parameters _empid, _empname, and _loc and you wish to by pass the parameter _empname, then the Snap should be configured as:


Pass through 





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.

Note

This property applies only to the Execute Snaps with SELECT statement or the Select Snaps. 

Default value: Not selected 


Multiexcerpt include macro
nameSnap Execution
pageSOAP Execute

Multiexcerpt include macro
nameExecution_Detail_Write
pageSOAP Execute


Note

 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 }


Note

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
namedownload_instructions
pageOpenAPI

Attachments
patterns*.zipslp, *.slpzip

Insert excerpt
SQL Server Snap Pack
SQL Server Snap Pack
nopaneltrue