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

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

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

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

大家可以看看示例:

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

 

 

然后给他定义一个类型:

 


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

 

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

 

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

 


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部分:

 


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

 

 

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

 


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

 

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

 


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

 

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

 


'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(2As Object
    vals(
0= i
    vals(
1= Chr(i + 90)
    vals(
2= System.DateTime.Now
    table.Rows.Add(vals)
Next

 

 

 


'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")

 

 


'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

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏