Using User-defined tables as stored procedure parameter

by | Dec 15, 2011 | BizTalk Community Blogs via Syndication | 0 comments

The WCF-SQL adapter provides support for multiple inserts through the Consume Adapter Service feature:

However, sometimes you might want to validate the data on the SQL side before before making the insert. For instance, if you have a collection of Customers, where some of them might already exist in the database, and should only be updated. In such a case, you’d have to first make a database lookup, to determine the state of the Customer and then make either an insert or update.

In such a case, using user-defined table types might be your solution. User-defined tables are similar to ordinary tables, but can be passed in as a parameter.

In my sample, I have a Contacts table, and I’m receiving a collection of Persons where some entities are new and some are to be updated.

Create the User-Defined Table Type

The user-defied table type will serve as our contract. 

CREATE TYPE [dbo].[InsertContactRequest] AS TABLE
(
    [PersonNo] [varchar](50) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [Phone] [varchar](50) NOT NULL,
    PRIMARY KEY CLUSTERED ([PersonNo] ASC)WITH (IGNORE_DUP_KEY = OFF)
)

Create the Stored Procedure

The stored procedure takes the user-defined table type as a parameter (@insertContactRequest), then updates all existing rows and inserting all new once.

CREATE PROCEDURE [dbo].[sp_InsertContacts] @insertContactRequest InsertContactRequest READONLY
AS
BEGIN
    
    UPDATE dbo.Contacts 
    SET Phone = r.Phone
    FROM dbo.Contacts c
    JOIN @insertContactRequest r on r.PersonNo = c.PersonNo

    INSERT INTO dbo.Contacts (PersonNo, FirstName, LastName, Phone)
    SELECT r.PersonNo, r.FirstName, r.LastName, r.Phone
    FROM    @insertContactRequest r
    WHERE    r.PersonNo not in(SELECT PersonNo FROM dbo.Contacts)
       
END

Generate BizTalk artefacs

1. In you Visual Studio, right-click the BizTalk project and select Add->Add Generated Items. Select Consume Adapter Service.

2. In the Consume Adapter Service dialog, click the configure button to set the credentials. Click Ok, and then Connect.

3. In the tree-view, select Strongly Typed Procedures, and select your stored procedure in the right pane. Click Add and Ok to generate the schemas.

4. Make your transformation, and complete your solution.

 

Here is the sample source.

HTH

(Kudos Daniel %u00d6stberg)

Blog Post by: wmmihaa

Submit a Comment