Using User-defined tables as stored procedure parameter
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
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)
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.
(Kudos Daniel Östberg)