What’s New in SQL 2008 – Part 3 – Table Type

The new Table type allows you to save a table definition in the database which can be reused later to define table variables, stored procedure parameters and functions. To create a Type, we use the CREATE TYPE clause, for example:

CREATE TYPE dbo.ProductList as TABLE (

    ProductName varchar(200) NULL,

    ProductCategory varchar(200) NOT NULL)

Important Notes:

• The metadata is visible using sys.table_types
  

• Variables/parameters cannot be set to NULL – if no table is supplied, an empty table is generated
  

• Not affected by transaction rollbacks
  

• Do not cause recompilations
  

Examples

Create Table-Type Variables	DECLARE @t as dbo.ProductList; INSERT INTO @t VALUES ('Grey Slate Paver 10x10','Paver'), ('Blue Slate Paver 10x10','Paver'), ('Heritage Blue 12x6','Brick')
Creating a Stored Procedure with a Table-typed Parameter	CREATE PROC dbo.uspGetOrdersforProduct ( @t as dbo.ProductList READONLY, @StartDate as Date, @EndDate as Date ) AS SELECT o.OrderId, o.OrderDate, o.CustomerId, o.ProductId, o.Amount, o.Qty FROM tOrder as o INNER JOIN tProduct t on t.ProductId = o.ProductId INNER JOIN @t as f on f.ProductCategory = t.ProductCategory and (t.ProductName is NULL or t.ProductName = f.ProductName) WHERE o.OrderDate between @StartDate and @EndDate
Passing Table-parameters into Stored Procedures	DECLARE @t as dbo.ProductList; INSERT INTO @t VALUES (NULL,'Paver'), (NULL,'Brick') EXEC dbo.uspGetOrdersforProduct @t