Using User-defined tables as stored procedure parameter

Listen with webReader
Published 15 December 11 01:47 PM | wmmihaa

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

image

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.

image

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.

image

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

image

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.

image

4. Make your transformation, and complete your solution.

image

 

Here is the sample source.

HTH

(Kudos Daniel Östberg)

Filed under: , ,

Comments

No Comments

This Blog

News

    MVP - Microsoft Most Valuable Professional BizTalk User Group Sweden BizTalk blogdoc

    Follow me on Twitter Meet me at TechEd

    Visitors

    Locations of visitors to this page

    Disclaimer

    The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

Syndication