With the introduction of SQL Server 2008 came the ability to define an entire table as a parameter, think of it like a table data type. This feature greatly eases the development process as constructing and parsing XML data strings is no longer necessary.

Table parameters are user defined parameters, i.e. you are creating a means of storing specific data that is passed by a stored procedure or function.

Limitations:

• The READONLY clause must be used when passing in the table valued variable
• Data in the table variable cannot be modified
• The table variables cannot be used as OUTPUT parameters only input parameters.
• When data is passed to the table variable the table variable must be passed to the stored procedure in the same batch. Table variables go out of scope as soon as the procedure or batch returns.

The following is a complete end to end example of how to create and pass data to table parameters:

IF OBJECT_ID('OrderHistory') > 0
	DROP TABLE OrderHistory;
GO

CREATE TABLE [dbo].[OrderHistory] (
	[OrderID] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY
	,[Product] [varchar](10) NULL
	,[OrderDate] [datetime] NULL
	,[SalePrice] [money] NULL
	)
GO

CREATE TYPE OrderHistoryTableType AS TABLE (
	[Product] [varchar](10) NULL
	,[OrderDate] [datetime] NULL
	,[SalePrice] [money] NULL
	)
GO

CREATE PROCEDURE usp_InsertOrder (@TableVariable OrderHistoryTableType READONLY)
AS
BEGIN
	INSERT INTO OrderHistory (
		Product
		,OrderDate
		,SalePrice
		)
	SELECT Product
		,OrderDate
		,SalePrice
	FROM @TableVariable
END
GO

DECLARE @DataTable AS OrderHistoryTableType

INSERT INTO @DataTable (
	Product
	,OrderDate
	,SalePrice
	)
VALUES (
	'Desktop'
	,GETUTCDATE()
	,599.00
	)

INSERT INTO @DataTable (
	Product
	,OrderDate
	,SalePrice
	)
VALUES (
	'Laptop'
	,GETUTCDATE()
	,299.00
	)

INSERT INTO @DataTable (
	Product
	,OrderDate
	,SalePrice
	)
VALUES (
	'Mouse'
	,GETUTCDATE()
	,9.00
	)

EXEC usp_InsertOrder @TableVariable = @DataTable

SELECT *
FROM OrderHistory

If you want to view other types of table type definitions in your system, or you’ve forgotten what you called a specific table parameter, you can execute the following query, which looks in the system catalog:

SELECT * FROM sys.table_types