Polling Stored Procedures having Table valued Parameters

Posted: November 6, 2013  |  Categories: Adapters Uncategorized

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

BizTalk360
BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top