SQL Server 2008数据库中使用表值参数

核心提示：在SQL Server 2005或更早的版本中的数据库中，表变量是不能作为存储进程的参数的。SQL Server 2008中的T-SQL功能新增了表值参数。利用这个新增特性，我们可以很方便的将1个表作为参数传给存储进程，减少了利用程序与SQL Server数据库服务器之间的交互，提升了程序性能

在SQL Server 2005或更早的版本中的数据库中，表变量是不能作为存储进程的参数的。当多行数据到SQL Server需要发送多行数据到SQL Server ，开发者要末每次发送1列记录，或想出其他的变通方法，以满足需求。固然在.net 2.0中提供了个SQLBulkCopy对象能够将多个数据行1次性传送给SQL Server，但是多行数据依然没法1次性传给存储进程。

SQL Server 2008中的T-SQL功能新增了表值参数。利用这个新增特性，我们可以很方便地通过T-SQL语句，或通过1个利用程序，将1个表作为参数传给存储进程。

1、用户自定义表类型

当第1次看看新的表值参数，我以为使用此功能有点复杂。有几个步骤。要做的第1件事是定义表型。在Management Studio 2008中的“Programmability”“Type”节点，您可以看到“User-Defined Table Types(用户自定义表类型)”，如图1所示 。

 图 1：用户自定义表类型

点击右键，在弹出菜单当选择“新用户定义的表型... ” ，会新建1个模板中的查询窗口，如图2所示 。

图 3：指定模板参数列的数值

在填写在适当的数值以后，点击肯定按钮，1个“CREATE TYPE”的声明取代了范本。这时候，你也能够手动增加1些列，或增加1些限制条件，最后点击肯定按钮。

以下是终极的代码：

以下为援用的内容： -- ================================ -- 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

在运行代码以后，对象的定义就建立好了，你可以在“User-Defined Table Type（用户自定义表类型”中查看属性，如图4所示，但没法修改它们。假设要修改的类型，你只能将其删除，然后依照修改后的属性再次创建它。

图4：查看用户自定义表类型的属性

2、使用用户自定义的表类型

假设打算在T-SQL代码中使用，您还必须创建1个新类型的变量，然后将具体的表的名称赋值给该变量。1旦赋值后，您可以在其他的T-SQL语句中使用它。由于它是1个变量，在批处理完成后，它也自动失效，结束生命周期。

请留意下面的代码，MyType是我们之前刚刚创建的数据类型。

以下为援用的内容： 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

在变量的有效范围内，你可以象操纵正常的表1样来操纵这个变量，如与另1个表象关联或将变量中的记录填充到另1个表。对表变量来讲，你没法修改表定义。

正如前面提到的，变量不能超出它的有效的范围。假设T-SQL脚本由多个批处理组成，变量只有在批处理内才能创建并有效使用。

1 2 下1页

核心提示：在SQL Server 2005或更早的版本中的数据库中，表变量是不能作为存储进程的参数的。SQL Server 2008中的T-SQL功能新增了表值参数。利用这个新增特性，我们可以很方便的将1个表作为参数传给存储进程，减少了利用程序与SQL Server数据库服务器之间的交互，提升了程序性能

3、使用变量作为参数

到目前为止，我们还没有看到常常表变量没法实现的功能。其好处是能够将变量作为参数传给存储进程。固然1个存储进程必须先建立，使用新的类型作为其中的1个参数。

下面这个例子，通过代码创建1个常规表，并对其填充记录。

以下为援用的内容： 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

请留意表值参数后面带了个READONLY参数。这是必须的，不能在例程体中对表值参数履行诸如 UPDATE、DELETE 或 INSERT 这样的 DML 操纵。

最后，我们对创建表值变量，对变量进行赋值，并调用存储进程。

以下为援用的内容： 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;

4、通过.net利用程序调用

表值参数这1特性最大的亮点在于可以在.net利用中使用表值参数。为了做到这1点，你必须要先安装.NET 3.5框架，并确保利用程序中已援用了 System.Data.SqlClient命名空间。创建表值参数时需要用到1些新的SQL数据类型（如DataTable、DataColumn等）。

首先创建1个本地数据表，并插进1些记录。肯定的是， DataTable中创建符适用户定义的表型的列计数和数据类型。

以下为援用的内容： '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

我们在代码中采取存储进程：创建1个命令对象，并新增两个参数。代码以下图所示：

以下为援用的内容： '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")

请留意@ MyTableParam参数的数据类型(SqlDbType.Structured)，这是.Net 3.5中新增的功能。最后，将当地表赋值给表值参数，并履行该命令。

以下为援用的内容： 'Set the value of the parameter param.Value = table 'Execute the query command.ExecuteNonQuery()

5、小结

SQL Server 2008中新增的表值参数特性，减少了利用程序与SQL Server数据库服务器之间的交互，提升了程序性能。

--------------------------------------------------------------

假设您有数据库资料、建议或是相干最新技术信息等等，请联系赛迪网IT技术--数据库频道，我们会在第1时间发布、分享给广大网友。

联系信箱：dongjw#staff.ccidnet.com (请将"#"改成"@")

上1页 1 2 唐山网站建设www.fw8.net
TAG:程序,进程,参数,类型,变量

评论加载中... 内容： 评论者： 验证码：