BizTalk Server Tutorial

BizTalk Server Concepts and Common Errors

Polling Stored Procedures having Table valued Parameters

I had an existing stored procedure which is taking table valued parameters and my requirement was to poll that stored procedure every 15 minutes using WCF-SQL typed Polling. Initially I thought it is not possible to poll a stored procedure accepting table valued parameters. (However it is straight forward to consume/call a stored procedure accepting table valued parameters.)
But my initial thoughts were wrong and we can poll stored procedures having table valued parameters just like any other stored procedure.
I created a sample table type like below.

USE [Test]
GO

CREATE TYPE [dbo].[TVP_Employee] AS TABLE(
[Name] [varchar](50) NULL
)
GO

I then created a sample stored procedure which takes this Table type as input.

USE [Test]
GO

CREATE PROCEDURE [dbo].[TVPTest]
@Param1 [TVP_Employee] READONLY
AS
BEGIN

SELECT * from Employee where Name in (select Name from @Param1)
END

GO

Now, I am polling this stored procedure in a WCF-SQL Receive Location using the below polling statement.

DECLARE @TVP_Employee [TVP_Employee]
INSERT INTO @TVP_Employee VALUES (‘Name2’)
INSERT INTO @TVP_Employee VALUES (‘3’)
INSERT INTO @TVP_Employee VALUES (‘2’)
EXEC TVPTest @param1 = @TVP_Employee

1

Basically, BizTalk will execute the entire script for polling the data. So, irrespective of just giving either a Select Statement or execute statement, we can define some other custom logic to pull data from SQL Database.
HTH

– Shiv

November 6, 2013 - Posted by | Adapters | , , , , ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: