表类型

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;