Problema

Existe um problema conhecido com tipos de tabela definidos pelo usuário como parâmetros para sp_executesql ?

Configurar script

Este script cria uma tabela, um procedimento e um tipo de tabela definido pelo usuário (restrito somente SQL Server 2008+).

• A finalidade do heap é fornecer uma auditoria de que sim, os dados foram inseridos no procedimento. Não há restrições, nem nada que impeça a inserção de dados.
• O procedimento toma como parâmetro um tipo de tabela definido pelo usuário. Tudo o que o proc faz é inserir na tabela.
• O tipo de tabela definido pelo usuário também é muito simples, apenas uma única coluna

Eu executei o seguinte contra 11.0.1750.32 (X64) e 10.0.4064.0 (X64)Sim, eu sei que a caixa pode ser corrigida, eu não controlo isso.

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

Reprodução de problemas

Este script demonstra o problema e deve levar cinco segundos para ser executado. Eu crio duas instâncias de meus tipos de tabela definidos pelo usuário e, em seguida, tento dois meios diferentes de passá-los para sp_executesql O mapeamento de parâmetro na primeira invocação imita o que capturei do SQL Profiler. Em seguida, chamo o procedimento sem o wrapper sp_executesql.

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=@p3
-- makes no matter if we're mapping variables
EXECUTE sp_executesql N'dbo.Repro',N'@MetricData dbo.UDTT READONLY',@MetricData

-- Five second delay
waitfor delay '00:00:05'
SELECT CONVERT(varchar(24), current_timestamp, 121) + ' Firing proc' AS commentary
-- this does
EXECUTE dbo.Repro @p3

-- Should only see the latter timestamp
SELECT * FROM dbo.UDTT_holder

GO

Resultados

Abaixo estão meus resultados. A tabela está inicialmente vazia. Eu emito a hora atual e faço as duas chamadas para sp_executesql. Aguardo 5 segundos para passar e emito o tempo atual, seguido de chamada do próprio procedimento armazenado e, finalmente, despejo a tabela de auditoria.

Como você pode ver no carimbo de data/hora, o registro para o registro B corresponde à invocação direta do procedimento armazenado. Além disso, não há nenhum registro SQLC.

ServerName                                       insert_time
------------------------------------------------------------------------

commentary
-------------------------------------------------
2012-02-02 13:09:05.973 Firing sp_executesql

commentary
-------------------------------------------------
2012-02-02 13:09:10.983 Firing proc

ServerName                                       insert_time
------------------------------------------------------------------------
SQLB\SQLB                                        2012-02-02 13:09:10.983

Derrubar roteiro

Este script remove os objetos na ordem correta (você não pode descartar o tipo antes que a referência do procedimento tenha sido removida)

-- cleanup
DROP TABLE dbo.UDTT_holder
DROP PROCEDURE dbo.Repro
DROP TYPE dbo.UDTT
GO

Por que isso importa

O código parece bobo, mas não tenho muito controle sobre o uso do sp_execute versus uma chamada "direta" para o proc. Mais acima na cadeia de chamadas, estou usando a biblioteca ADO.NET e transmitindo um TVP como fiz anteriormente . O tipo do parâmetro está definido corretamente como System.Data.SqlDbType.Structured e o CommandType está definido como System.Data.CommandType.StoredProcedure .