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 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应用程序调用
表值参数这一特性最大的亮点在于可以在.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数据库服务器之间的交互，提升了程序性能。
--------------------------------------------------------------
如果您有数据库资料、建议或是相关最新技术信息等等，请联系赛迪网IT技术--数据库频道，我们会在第一时间发布、分享给广大网友。
联系信箱：dongjw#staff.ccidnet.com (请将"#"改为"@")
(责任编辑：董建伟)
