表类型

　　表类型简化了表变量的定义。通过创建表类型，可以把表的定义保存到数据库中，以后在定义表变量，存储过程和用户定义函数的输入参数时，就可以将表类型作为表的定义而重用。

下面的示例演示了如何通过表类型来简化表变量的定义，并且如何重用它。

USE TSQLFundamentals2008;
GO

IF TYPE_ID('dbo.OrderTotalsByYear') IS NOT NULL DROP TYPE dbo.OrderTotalsByYear;
GO
-- 创建表类型
CREATE TYPE dbo.OrderTotalsByYear AS TABLE
(
    orderyear INT NOT NULL PRIMARY KEY,
    qty INT NOT NULL
);

-- 通过表类型创建表变量，并且填充数据
DECLARE @MyOrderTotalsByYear AS dbo.OrderTotalsByYear;

INSERT INTO @MyOrderTotalsByYear( orderyear,qty )
SELECT YEAR(orders.orderdate)AS orderdate,SUM(orderdetails.qty) AS qty FROM Sales.Orders AS orders
LEFT JOIN Sales.OrderDetails AS orderdetails ON orders.orderid = orderdetails.orderid
GROUP BY YEAR(orders.orderdate);

SELECT * FROM @MyOrderTotalsByYear;

查询结果：

转载于:https://www.cnblogs.com/mcgrady/p/3981868.html