SQL Server 2008数据库中如何使用表值参数(1)
在SQL Server 2005或更早的版本中的数据库中，表变量是不能作为存储过程的参数的。当多行数据到SQL Server需要发送多行数据到SQL Server ，开发者要么每次发送一列记录，或想出其他的变通 *** ，以满足需求。虽然在.net 2.0中提供了个SQLBulkCopy对象能够将多个数据行一次性传送给SQL Server，但是多行数据仍然无法一次性传给存储过程。
SQL Server 2008中的T-SQL功能新增了表值参数。利用这个新增特性，我们可以很方便地通过T-SQL语句，或者通过一个应用程序，将一个表作为参数传给存储过程。
1、用户自定义表类型
当之一次看看新的表值参数，我认为使用此功能有点复杂。有几个步骤。要做的之一件事是定义表型。在Management studio 2008中的“Programmability”“Type”节点，您可以看到“User-Defined Table Types(用户自定义表类型)”，如图1所示 。
图2：用户自定义表类型创建语句
点击“Specify Values for Template Parameters（指定值为模板参数）”按钮，将探出一个对话框，如图3所示。
图4：查看用户自定义表类型的属性
2、使用用户自定义的表类型
假如打算在T-SQL代码中使用，您还必须创建一个新类型的变量，然后将具体的表的名称赋值给该变量。一旦赋值后，您可以在其他的T-SQL语句中使用它。因为它是一个变量，在批处理完成后，它也自动失效，结束生命周期。
请注意下面的代码，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 |
在变量的有效范围内，你可以象操作正常的表一样来操作这个变量，如与另一个表象关联或者将变量中的记录填充到另一个表。对于表变量来说，你无法修改表定义。
正如前面提到的，变量不能超出它的有效的范围。假如T-SQL脚本由多个批处理组成，变量只有在批处理内才能创建并有效使用。
3、使用变量作为参数
到目前为止，我们还没有看到经常表变量无法实现的功能。其好处是能够将变量作为参数传给存储过程。当然一个存储过程必须先建立，使用新的类型作为其中的一个参数。
下面这个例子，通过代码创建一个常规表，并对其填充记录。
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 READON *** , @UserID varchar(20) AS NSERT INTO MyTable([col1],[col2],[col3],[UserID]) SELECT [col1],[col2],[col3],@UserID FROM @MyTableParam GO |
请注意表值参数后面带了个READON *** 参数。这是必需的，不能在例程体中对表值参数执行诸如 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应用程序调用
表值参数这一特性更大的亮点在于可以在.net应用中使用表值参数。为了做到这一点，你必须要先安装.NET 3.5框架，并确保应用程序中已经引用了 System.Data.SqlClient命名空间。创建表值参数时需要用到一些新的SQL数据类型（如DataTable、DataColumn等）。
首先创建一个本地数据表，并插入一些记录。肯定的是， 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 |
我们在代码中采用存储过程：创建一个命令对象，并新增两个参数。代码如下图所示：
'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数据库服务器之间的交互，提升了程序性能。
在SQL Server 2005或更早的版本中的数据库中，表变量是不能作为存储过程的参数的。当多行数据到SQL Server需要发送多行数据到SQL Server ，开发者要么每次发送一列记录，或想出其他的变通 *** ，以满足需求。虽然在.net 2.0中提供了个SQLBulkCopy对象能够将多个数据行一次性传送给SQL Server，但是多行数据仍然无法一次性传给存储过程。
SQL Server 2008中的T-SQL功能新增了表值参数。利用这个新增特性，我们可以很方便地通过T-SQL语句，或者通过一个应用程序，将一个表作为参数传给存储过程。
1、用户自定义表类型
当之一次看看新的表值参数，我认为使用此功能有点复杂。有几个步骤。要做的之一件事是定义表型。在Management Studio 2008中的“Programmability”“Type”节点，您可以看到“User-Defined Table Types(用户自定义表类型)”，如图1所示 。
图2：用户自定义表类型创建语句
点击“Specify Values for Template Parameters（指定值为模板参数）”按钮，将探出一个对话框，如图3所示。
图4：查看用户自定义表类型的属性
2、使用用户自定义的表类型
假如打算在T-SQL代码中使用，您还必须创建一个新类型的变量，然后将具体的表的名称赋值给该变量。一旦赋值后，您可以在其他的T-SQL语句中使用它。因为它是一个变量，在批处理完成后，它也自动失效，结束生命周期。
请注意下面的代码，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 |
在变量的有效范围内，你可以象操作正常的表一样来操作这个变量，如与另一个表象关联或者将变量中的记录填充到另一个表。对于表变量来说，你无法修改表定义。
正如前面提到的，变量不能超出它的有效的范围。假如T-SQL脚本由多个批处理组成，变量只有在批处理内才能创建并有效使用。
3、使用变量作为参数
到目前为止，我们还没有看到经常表变量无法实现的功能。其好处是能够将变量作为参数传给存储过程。当然一个存储过程必须先建立，使用新的类型作为其中的一个参数。
下面这个例子，通过代码创建一个常规表，并对其填充记录。
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 READON *** , @UserID varchar(20) AS NSERT INTO MyTable([col1],[col2],[col3],[UserID]) SELECT [col1],[col2],[col3],@UserID FROM @MyTableParam GO |
请注意表值参数后面带了个READON *** 参数。这是必需的，不能在例程体中对表值参数执行诸如 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应用程序调用
表值参数这一特性更大的亮点在于可以在.net应用中使用表值参数。为了做到这一点，你必须要先安装.NET 3.5框架，并确保应用程序中已经引用了 System.Data.SqlClient命名空间。创建表值参数时需要用到一些新的SQL数据类型（如DataTable、DataColumn等）。
首先创建一个本地数据表，并插入一些记录。肯定的是， 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 |
我们在代码中采用存储过程：创建一个命令对象，并新增两个参数。代码如下图所示：
'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数据库服务器之间的交互，提升了程序性能。
扫描二维码推送至手机访问。
版权声明：本文由2345好导航站长资讯发布，如需转载请注明出处。
“SQL Server 2008数据库中如何使用表值参数(1)” 的相关文章
windows 7查看电脑近期使用情况确定有没有被他人使用
我一个朋友最近装装了Windows 7，他总感觉有人用了他的电脑，但是一直不确定，他想知道有没有人动用他的电脑，问我有没有办法 我说设置一下就行了： 启动Windows 7，在搜索栏中输入编辑组，马上就搜索到了编辑组策略，点击即可启动程序编辑组策略。依次展开组策略左侧树形列表的计算机配置/管理模...
各个都很实用:windows 7系统电脑日常小技巧
在使用Windows 7的时候很多用户对于系统的很多功能并不是很了解，也正是因为这样的不了解导致很多用户在使用Win 之后并不能体验Windows 7的功能便携和强大功能，导致很多用户对于Windows 7的认识还是比较模糊，不过为了让更多的朋友了解Windows 7的特点和便携应用，今天就为大家汇...
微软今天正式停止对windows 7的主流支持
北京时间1月13日消息，根据计划，微软将于2015年1月13日正式结束对Windows 7SP1的“主流支持”，而这也标志着“扩展支持”阶段的开始，这个阶段将于2020年1月14日结束。 2013年10月份，微软终止销售独立的Windows 7系统安装包，并要求制造商于2014年10月份停止生...
windows 7系统件夹和文件都不显示名字如何解决
假如碰到电脑里的文件夹和文件全都不显示名字了(如下图)，是不是电脑中毒了呢 请教下笔者有没好的解决方法!下面是笔者给出的答案，希望能够帮助到大家! 首先打开你用来装图片的文件夹(缩略图下面的文字不显示的文件夹)，然后但击工具栏上的“查看”选择“平铺”， 下面就是关键：要按住SHIFT键不...
windows 7系统开机后出现黑屏提示Windows无法启动
一些用户的Windows 7系统开机后出现黑屏，代码为 Windows无法启动： WindowsSystem32ConfigSystem”。这个问题该如何修复呢？现在小编就给大家分析一下吧。 其实这个代码的意思是引导文件丢失了，引导文件（NTLDR）一般存放于C盘根目录下，是一个具有隐藏和只读属性...
windows 7系统怎么取消禁ping命令？
当我在虚拟机的linux系统中ping本机的ip发现ping不通，而本机可以ping通虚拟机中的ip。应该是出于安全考虑吧，Windows 7默认在防火墙里禁止了。 1、查看主机ip 打开“开始”程序中的“附件”，找到“命令提示符”打开。输入命令：ipconfig，可以看到本机的ip是192.1...