使用 tables 作為表值參數 (TVP)

MS SQL 2008 支持 TVP：將數據批量上傳到儲存過程進行處理的有用功能。

是否可以利用現有的表定義，而不是創建使用者定義的類型？例如，是否可以創建具有以下簽名的儲存過程？

CREATE PROCEDURE usp_InsertProductionLocation
@TVP **LocationTable** READONLY

該文件似乎表明這是不可能的。

範常式式碼

/*
Sample code from:
http://msdn.microsoft.com/en-us/library/bb510489.aspx
*/

USE AdventureWorks2008R2;
GO

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
   @TVP LocationTableType READONLY
   AS 
   SET NOCOUNT ON
   INSERT INTO [AdventureWorks2008R2].[Production].[Location]
          ([Name]
          ,[CostRate]
          ,[Availability]
          ,[ModifiedDate])
       SELECT *, 0, GETDATE()
       FROM  @TVP;
       GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP 
AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
   SELECT [Name], 0.00
   FROM 
   [AdventureWorks2008R2].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

/*
The following is not part of the original source code:
*/

CREATE TABLE LocationTable(
LocationName VARCHAR(50)
, CostRate INT );
GO

不，您不能利用現有的表定義，您需要定義一個顯式類型。這早在 2007 年就被要求並以“無法修復”而關閉，但我仍然強烈建議您投票並發表評論，描述您的案例以及這將如何幫助您的企業提高生產力。你甚至可以指出這個問題來證明嘗試和自動化它是多麼乏味。

• 聲明一個變數…（以前是 UserVoice #32891356（以前是 Connect #294130））

不過，您今天可以動態執行此操作……例如，對於您的簡單定義：

-- you would pass these two in as parameters of course:
DECLARE
 @TableName SYSNAME = N'LocationTable',
 @TypeName  SYSNAME = N'LocationTypeTable';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + N',' + CHAR(13) + CHAR(10) + CHAR(9) 
   + QUOTENAME(c.name) + ' '
   + s.name + CASE WHEN LOWER(s.name) LIKE '%char' THEN 
       '(' + CONVERT(VARCHAR(12), (c.max_length/
       (CASE LOWER(LEFT(s.name, 1)) WHEN N'n' THEN 2 ELSE 1 END))) + ')' 
       ELSE '' END
       -- need much more conditionals here for other data types
   FROM sys.columns AS c
   INNER JOIN sys.types AS s
   ON c.system_type_id = s.system_type_id
   AND c.user_type_id = s.user_type_id
   WHERE c.[object_id] = OBJECT_ID(@TableName);

SELECT @sql = N'CREATE TYPE ' + @TypeName
   + ' AS TABLE ' + CHAR(13) + CHAR(10) + '(' + STUFF(@sql, 1, 1, '')
   + CHAR(13) + CHAR(10) + ');';

PRINT @sql;
-- EXEC sp_executesql @sql;

結果：

CREATE TYPE LocationTypeTable AS TABLE 
(
   [LocationName] varchar(50),
   [CostRate] int
);

免責聲明：這並不涉及所有其他類型的事情，例如 MAX 類型、數字的精度和小數位數等。最終的解決方案必須更加健壯以考慮所有潛在的列定義，但這應該給你一個開始。

在 SQL Server 2012 中，有新的 DMV 和儲存過程可以更輕鬆地從現有表（或儲存過程甚至臨時查詢）派生列元數據，而不必弄亂針對 sys.types 和 sys 的所有條件邏輯。列。我在 12 月簡要介紹了這些增強功能。它仍然很乏味，但它介於上面可怕的無法維護的意大利麵條和只是說“作為副本的能力”之間

$$ table x $$“……

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