問題

使用者定義的表類型作為sp_executesql的參數是否存在已知問題？

設置腳本

此腳本創建表、過程和使用者定義的表類型中的每一個（僅限 SQL Server 2008+）。

• 堆的目的是提供一個審計，是的，數據進入了程序。沒有任何限制，沒有任何東西可以阻止數據被插入。
• 該過程將使用者定義的表類型作為參數。proc 所做的只是插入到表中。
• 使用者定義的表類型也很簡單，只有一列

我已經針對以下內容執行了11.0.1750.32 (X64) ，10.0.4064.0 (X64)是的，我知道可以修補該框，但我無法控制。

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

問題再現

此腳本展示了該問題，執行需要五秒鐘。我創建了我的使用者定義表類型的兩個實例，然後嘗試了兩種不同的方法將它們傳遞給sp_executesql 第一次呼叫中的參數映射模仿了我從 SQL Profiler 擷取的內容。然後我在沒有 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

結果

下面是我的結果。該表最初是空的。我發出目前時間並對 sp_executesql 進行兩次呼叫。我等待 5 秒過去並發出目前時間，然後呼叫儲存過程本身，最後轉儲審計表。

從時間戳可以看出，B 記錄的記錄對應於直接的儲存過程呼叫。此外，沒有 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

拆掉腳本

此腳本以正確的順序刪除對象（在刪除過程的引用之前不能刪除類型）

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

為什麼這很重要

程式碼看起來很傻，但我對 sp_execute 的使用與對 proc 的“直接”呼叫沒有太多控制。在呼叫鏈的上層，我正在使用 ADO.NET 庫並像以前一樣傳入 TVP 。參數的類型正確設置為System.Data.SqlDbType.Structured並且 CommandType 設置為System.Data.CommandType.StoredProcedure。

sp_executesql用於執行臨時 T-SQL。所以你應該嘗試：

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

我很想刪除這個問題，但認為其他人可能會遇到類似的情況。

決議說明

Rob 和 Martin Smith（已刪除答案 10k+）看到了我沒有看到的內容 - 傳遞給 sp_executesql 的語句沒有使用該@MetricData參數。未傳遞/映射的參數不會被使用。

根本原因是我$updateCommand沒有設置CommandType。預設值為 Text，因此我的儲存過程被正確呼叫。我的參數正在被創建和傳遞，但正如其他答案中所述，僅僅因為參數可用並不意味著它們正在被使用。

程式碼以防有人對我的錯誤感興趣

   $updateConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=localhost\localsqla;Initial Catalog=baseline;Integrated Security=SSPI;")
   $updateConnection.Open()

   $updateCommand = New-Object System.Data.SqlClient.SqlCommand($updateQuery)
   $updateCommand.Connection = $updateConnection

   # This is what I was missing
   $updateCommand.CommandType = [System.Data.CommandType]::StoredProcedure
   #$updateCommand.CommandType = [System.Data.CommandType]::Text
   #$updateCommand.CommandType = [System.Data.CommandType]::TableDirect

   $DataTransferFormat = $updateCommand.Parameters.AddWithValue("@MetricData", $dataTable)
   $DataTransferFormat.SqlDbType = [System.Data.SqlDbType]::Structured
   $DataTransferFormat.TypeName = $udtt
   $results = $updateCommand.ExecuteNonQuery()

使用 Text 的 CommandType 值執行將需要 @rob_farley 將值更改$updateQuery為“EXEC dbo.Repro @MetricData”的解決方案。那時， sp_executesql 將正確映射值並且生活很好。

如文件所示，SqlClient 數據提供程序不支持使用CommandTypeof執行。TableDirect

使用CommandTypeofStoredProcedure轉換為直接呼叫沒有sp_executesql包裝器的儲存過程，並且效果很好。

引用自：https://dba.stackexchange.com/questions/12139