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

One Platform Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

One Platform - Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

One Platform - Operations, Monitoring and Analytics Software
ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

Back to Top