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
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
