[SQL]SQL Server 2008中的用户自定义表类型

目前的批量删除和批量更新，很是烦人，解决方案无非三种：

XML，SQL自定义函数split，和CLR实现split。这几种都比较烦人，代码很多，维护麻烦，很不爽。

现在SQL2008新增的一个功能，我也不知道中文名怎么翻译，暂且叫他表参数吧。

大家可以看看示例：

这个就是用户定义的表类型：

然后给他定义一个类型：

Code
— ================================
— Create User-defined Table Type
— ================================
USE Test
GO
— Create the data type
Create TYPE dbo.MyType AS TABLE 
(
    col1 int NOT NULL, 
    col2 varchar(20) NULL, 
    col3 datetime NULL, 
    PRIMARY KEY (col1)
)
GO

可以看到，生成的表类型的组成情况，并且居然可以给表类型建立索引，呵呵

这个是操作 表类型的脚本：

Code
DECLARE @MyTable MyType
Insert INTO @MyTable(col1,col2,col3)
VALUES (1,'abc','1/1/2000'),
    (2,'def','1/1/2001'),
    (3,'ghi','1/1/2002'),
    (4,'jkl','1/1/2003'),
    (5,'mno','1/1/2004')
    
Select * FROM @MyTable 

下面演示如何将表参数作为一个存储过程参数传递，以及ADO.NET的代码

SQL部分：

Code
USE [Test]
GO
Create TABLE [dbo].[MyTable] (
    [col1] [int] NOT NULL PRIMARY KEY,
    [col2] [varchar](20) NULL,
    [col3] [datetime] NULL,
    [UserID] [varchar] (20) NOT NULL
    )    
GO
Create PROC usp_AddRowsToMyTable @MyTableParam MyType READONLY,
    @UserID varchar(20) AS
    
    Insert INTO MyTable([col1],[col2],[col3],[UserID])
    Select [col1],[col2],[col3],@UserID    
    FROM @MyTableParam
    
GO

如何在sql中调用此存储过程：

Code
DECLARE @MyTable MyType
Insert INTO @MyTable(col1,col2,col3)
VALUES (1,'abc','1/1/2000'),
    (2,'def','1/1/2001'),
    (3,'ghi','1/1/2002'),
    (4,'jkl','1/1/2003'),
    (5,'mno','1/1/2004')
EXEC usp_AddRowsToMyTable @MyTableParam = @MyTable, @UserID = 'Kathi'
Select * FROM MyTable

其中还涉及到一个权限问题，需要执行以下代码：

Code
GRANT EXECUTE ON TYPE::dbo.MyType TO TestUser;

从.net app那调用此存储过程：

Code
'Create a local table
Dim table As New DataTable("temp")
Dim col1 As New DataColumn("col1", System.Type.GetType("System.Int32"))
Dim col2 As New DataColumn("col2", System.Type.GetType("System.String"))
Dim col3 As New DataColumn("col3", System.Type.GetType("System.DateTime"))
table.Columns.Add(col1)
table.Columns.Add(col2)
table.Columns.Add(col3)
        
'Populate the table
For i As Integer = 20 To 30
    Dim vals(2) As Object
    vals(0) = i
    vals(1) = Chr(i + 90)
    vals(2) = System.DateTime.Now
    table.Rows.Add(vals)
Next

Code
'Create a command object that calls the stored proc
Dim command As New SqlCommand("usp_AddRowsToMyTable", conn)
command.CommandType = CommandType.StoredProcedure
'Create a parameter using the new type
Dim param As SqlParameter = command.Parameters.Add("@MyTableParam", SqlDbType.Structured)
command.Parameters.AddWithValue("@UserID", "Kathi")

Code
'Set the value of the parameter
param.Value = table
'Execute the query
command.ExecuteNonQuery()

详情可以参看：

http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters