SQLServer2008如何把表格变量传递到存储过程中(批量插入).doc

实例讲解如何把表格变量传递到存储过程中在Microsoft SQL Server 2008中，你可以实现把表格变量传递到存储过程中，如果变量可以被声明，那么它就可以被传递。下面我们来具体介绍如何把表格变量(包括内含的数据)传递到存储过程和功能中去。 传递表值参数 用户经常会碰到许多需要把数值容器而非单个数值放到存储过程里的情况。对于大部分的编程语言而言，把容器数据结构传递到例程里或传递出来是很常见而且很必要的功能。TSQL也不例外。 SQL Server 2000通过OPENXML可以实现这个功能，用户可以把数据存储为VARCHAR数据类型然后进行传递。到了SQL Server 2005，随着 XML数据类型以及XQuery的出现，这个功能变得容易一点。但用户仍然需要对XML数据进行组建和粉碎才能够使用它，因此这个功能使用起来并不简单。SQL Server 2008则能够把表值数据类型传递到存储过程和功能中，从而大大地简化了编程的工作，因为程序员无需再花心思去组建和解析XML数据了。该功能还可以让客户方开发员传递客户方数据表格到数据库中。 怎样传递表格参数? 以销售为例，首先建立一个 my SalesHistory表格，里面包含了产品销售的信息。写以下脚本就可以在数据库里创建你选择的表格： IF OBJECT_ID(SalesHistory)0 DROP TABLE SalesHistory; GO CREATE TABLE dbo.SalesHistory ( SaleID int IDENTITY(1,1) NOT NULL PRIMARY KEY, Product varchar(10) NULL, SaleDate datetime NULL, SalePrice money NULL ) GO 建立表值参数第一步是创建确切的表格类型，这一步非常重要，因为这样你就可以在数据库引擎里定义表格的结构，让你可以在需要的时候在过程代码里使用该表格。下面的代码创建 SalesHistoryTableType 表格类型定义： CREATE TYPE SalesHistoryTableType AS TABLE (Product varchar(10) NULL, SaleDate datetime NULL, SalePrice money NULL ) GO 如果想要查看系统里其他类型的表格类型定义，你可以执行下面这个查询命令，查看系统目录： SELECT * FROM sys.table_types 我们需要定义用来处理表值参数的存储过程。下面这个程序能够接受指定SalesHistoryTableType类型的表值参数，并加载到SalesHistory中，表值参数在Product列中的值为“BigScreen”： CREATE PROCEDURE usp_InsertBigScreenProducts ( TableVariable SalesHistoryTableType READONLY ) AS BEGIN INSERT INTO SalesHistory ( Product, SaleDate, SalePrice ) SELECT Product, SaleDate, SalePrice FROM TableVariable WHEREProduct = BigScreen END GO 传递的表格变量还可以用做任何其他表格的查询数据。 传递表值参数功能的局限性 在传递表值变量到程序中时必须使用 READONLY从句。表格变量里的数据不能做修改除了修改你可以把数据用于任何其他的操作。另外，你也不能把表格变量用做OUTPUT参数只能用做input参数。 使用自己的新表格变量类型 首先，要声明一个变量类型SalesHistoryTableType，不需要再一次定义表格结构，因为在创建这个表格类型的时候已经定义过了。 DECLARE DataTable AS SalesHistoryTableType The following script adds 1,000 records into my DataTable table variable: DECLARE i SMALLINT SET i = 1 WHILE (i =1000) BEGIN INSERT INTO DataTable(Product, SaleDate, SalePrice) VALUES (Computer, DATEADD(mm, i, 3/11/1919), DATEPART(ms, GETDATE() + (i + 57) INSERT INTO DataTable(Product, SaleDate, SalePrice) VALUES(BigScreen, DATEADD(mm, i, 3/11/1927), DATEPART(ms, GETDATE() + (i + 13) INSERT INTO DataTable(Product, SaleDate, SalePrice) VALUES(PoolTable, DATEADD(mm, i, 3/11/1908), DATEPART(ms, GETDATE() + (i + 29) SET i = i + 1 END 只要把数据加载到表格变量里，就可以把结构传递到存储过程中。 注意：当表格变量作为参数传递后，表格会在存储在tempdb系统数据库里，而不是传递整个数据集在内存里。因为这样保证高效处理大批量数据。所有服务器方的表格变量参数传递都是通过使用reference调用tempdb中的表格。 EXECUTE usp_InsertBigScreenProducts TableVariable = DataTable 想要查询程序是否和预想效果一样，可以执行以下查询来看记录是否已经插入到 SalesHistory表格中： SELECT * FROM SalesHistory 结论： 虽然SQL Server 2008数据库的参数传递功能的使用还有一些局限性，比如不能修改参数中的数据和把变量用于output，但它已经很大程度的提高了程序性能，它可以减少server往返旅程数、利用表格限制并扩展编程在数据库引擎中的功能。如何在C#中傳遞表變量到存儲過程請看以下代碼:1. 假定後台定義的表類型為create type CustomerType as Table (CustomerID varchar(50),CustomerName varchar(50)2. 假定存儲過程只有一個參數customer, 類型為 CustomerTypecreate procedure up_InsertCustomer (customer CustomerType READONLY)ASbegininsert into customerbase select customerid,customername from customerend3. 直接在SQL中可用以下T-SQL語句調用declare c as CustomerTypeinsert into c(customerid,customername)select 1111,dasfasdfunionselect 2222,w5etaretwexec up_insertcustomer c4. 在前台調用的代碼為: SqlConnection conn = new SqlConnection(server=cn110088;database=test;uid=sa;password=amei); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = up_InsertCustomer; SqlParameter para1 = new SqlParameter(customer, SqlDbType.Structured); DataTable dt = new DataTable(); dt.Columns.Add(customerid); dt.Columns.Add(customername); DataRow dr = dt.NewRow(); drcustomerid = 00001; drcustomername = NOKIA; dt.Rows.Add(dr); dr = dt.NewRow(); drcustomerid = 00002; drcustome