sp_executesql with user defined table type not behaving correctly

Problem

Is there a known issue with user defined table types as parameters to sp_executesql?
Set up script

This script creates one each of table, procedure and user defined table type (restricted SQL Server 2008+ only).

• The heap's purpose is to provide an audit that yes, the data made it into the procedure. There are no constraints, no nothing to prevent data from being inserted.

• The procedure takes as a parameter a user defined table type. All the proc does is insert into the table.

• The user defined table type is bog simple as well, just a single column

I have run the following against 11.0.1750.32 (X64) and 10.0.4064.0 (X64) Yes, I know that box could be patched, I don't control that.

-- this table record that something happened
CREATE TABLE dbo.UDTT_holder
(
    ServerName varchar(200)
,   insert_time datetime default(current_timestamp)
)
GO

-- user defined table type transport mechanism
CREATE TYPE dbo.UDTT
AS TABLE
(
    ServerName varchar(200)
)
GO

-- Stored Procedure to reproduce issue
CREATE PROCEDURE dbo.Repro
(
    @MetricData dbo.UDTT READONLY
)
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO dbo.UDTT_holder 
    (ServerName)
    SELECT MD.* FROM @MetricData MD
END
GO

Problem reproduction

This script demonstrates the problem and should take five seconds to execute. I create two instances of my user defined table types and then try two different means of passing them in to sp_executesql The parameter mapping in the first invocation mimics what I captured from SQL Profiler. I then call the procedure without the sp_executesql wrapper.

```
SET NOCOUNT ON
DECLARE
@p3 dbo.UDTT
, @MetricData dbo.UDTT
INSERT INTO @p3 VALUES(N'SQLB\SQLB')
INSERT INTO @MetricData VALUES(N'SQLC\SQLC')

-- nothing up my sleeve
SELECT * FROM dbo.UDTT_holder

SELECT CONVERT(varchar(24), current_timestamp, 121) + ' Firing sp_executesql' AS commentary
-- This does nothing
EXECUTE sp_executesql N'dbo.Repro',N'@MetricData dbo.UDTT READONLY',@MetricData

sp_executesql is for executing ad-hoc T-SQL. So you should try:

EXECUTE sp_executesql N'exec dbo.Repro @MetricData',N'@MetricData dbo.UDTT READONLY',@MetricData=@p3

StackExchange Database Administrators Q#12139, answer score: 12