使用表值参数(用户自定义表类型).

使用自定义表类型（SQL Server 2008）

在 SQL Server 2008 中，用户定义表类型是指用户所定义的表示表结构定义的类型。您可以使用用户定义表类型为存储过程或函数声明表值参数，或者声明您要在批处理中或在存储过程或函数的主体中使用的表变量。

若要创建用户定义表类型，请使用 CREATE TYPE 语句。

创建表值类型
CREATE TYPE StudentTableType AS TABLE --创建表值类型
(
StudentID int,
ClassID int,
Mark int
)

使用表值参数的存储过程
CREATE PROC AddStudent
@stuTable StudentTableType READONLY --表值参数作为存储过程的参数
AS
INSERT INTO Student
SELECT *
FROM @stuTable --使用传入的表值参数

调用有表值参数的存储过程
DECLARE @stuTable StudentTableType --定义表值变量
INSERT INTO @stuTable --初始化表值变量内容
VALUES (20,1,95),(21,1,78),(22,2,88)
--调用有表值参数的存储过程
EXEC AddStudent @stuTable

下面我用一个实例来讲解一下

-- ================================
-- 创建和使用自定义表类型
--
-- ================================

USE master
GO
-- ================================
-- 创建测试数据库
-- ================================
CREATE DATABASE demo
GO

-- ================================
-- 创建一个表
-- ================================
USE demo
GO

CREATE TABLE Customers
(
Id int NOT NULL,
Name char(10) NULL,
PRIMARY KEY (Id)
)
GO

USE demo
GO
-- ================================
-- 创建自定义表类型
-- ================================
CREATE TYPE dbo.CustomerTable AS TABLE
(
Id int NOT NULL,
Name char(10) NULL,
PRIMARY KEY (Id)
)
GO

-- =================================
-- 直接使用自定义表类型
-- =================================
DECLARE @c CustomerTable
INSERT INTO @c VALUES(1,'Xizhang')
SELECT * FROM @c

-- =================================
-- 在存储过程中使用自定义表类型
-- =================================
CREATE PROC GetCustomers
(@c CustomerTable READONLY)
AS
INSERT Customers SELECT * FROM @c --将传过来的参数（其实是一个表）的数据插入到Customers表里面去

-- =================================
-- 调用该存储过程，一次性插入4行数据
-- =================================
DECLARE @temp CustomerTable
INSERT INTO @temp VALUES(7,'Xizhang')
INSERT INTO @temp VALUES(2,'Xizhang')
INSERT INTO @temp VALUES(3,'Xizhang')
INSERT INTO @temp VALUES(4,'Xizhang')
EXEC GetCustomers @c=@temp
SELECT * FROM Customers

-- =================================
-- 清理数据库
-- =================================
USE master
GO

DROP DATABASE demo
GO

看起来不错对吧，但是你应该马上想到一个问题，如果说这个存储过程要在客户端代码中调用，那么该怎么提供这个参数值呢？

using System.Data.SqlClient;
using System.Data;

class Program
{
static void Main(string[] args)
{
DataTable tb = GetData();
using (SqlConnection conn = new SqlConnection("server=sql2008;database=demo;integrated security=true"))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "GetCustomers";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@c", SqlDbType.Structured);//这个类型很关键
param.Value = tb;
cmd.Parameters.Add(param);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}

Console.WriteLine("完成操作");
Console.Read();
}

private static DataTable GetData()
{
DataTable tb = new DataTable();
tb.Columns.Add("Id",typeof(int));
tb.Columns.Add("Name", typeof(string));

//添加100个客户资料
for (int i = 0; i < 100; i++)
{
DataRow row = tb.NewRow();
row[0] = i;
row[1] = "Name " + i.ToString();
tb.Rows.Add(row);
}

return tb;
}
}

这样做实在是太棒了，可以一次性写入100行数据呢？我们再来看看在服务端到底发生了什么事情
实际上，在服务端确实会有一个定义临时变量的过程，然后把所有的数据插入到这个变量中去，然后再执行存储过程的

1. 客户端是否一定用DataTable类型

-- 推荐使用DataTable类型，但也可以使用其他的类型，例如DataReader的数据流

2. DataTable的字段名称是否要匹配

--不一定。只要顺序一致，类型一样就可以了。

有一个参考的blog，请看下面的链接

http://msdn.microsoft.com/zh-cn/library/bb675163.aspx