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

在 SQL Server 2008 中，用户定义表类型是指用户所定义的表示表结构定义的类型。您可以使用用户定义表类型为存储过程或函数声明表值参数，或者声明您要在批处理中或在存储过程或函数的主体中使用的表变量。有关如何定义表结构的详细信息，请参阅 CREATE TABL

在 SQL Server 2008 中，用户定义表类型是指用户所定义的表示表结构定义的类型。您可以使用用户定义表类型为存储过程或函数声明表值参数，或者声明您要在批处理中或在存储过程或函数的主体中使用的表变量。有关如何定义表结构的详细信息，请参阅 CREATE TABLE (Transact-SQL)。

若要创建用户定义表类型，请使用 CREATE TYPE 语句。为了确保用户定义表类型的数据满足特定要求，您可以对用户定义表类型创建唯一约束和主键。

有关与用户定义类型相关联的目录视图的信息，请参阅 sys.types 和 sys.table_types。

限制

用户定义表类型具有下列限制：

安全性

用户定义表类型的权限通过使用下列 Transact-SQL 关键字来遵循 SQL Server 的对象安全模式：CREATE、GRANT、DENY、ALTER、CONTROL、TAKE OWNERSHIP、REFERENCES、EXECUTE、VIEW DEFINITION 和 REVOKE。

 

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

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

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));