syntaxhelp.com Your online cheat sheet!

Checking if the User Defined Table Type Exists (and drop if exists)

IF EXISTS(
	SELECT * FROM sys.types 
	WHERE name = 'OrderDetailsType'
)DROP TYPE dbo.OrderDetailsType
GO

Creating a User Defined Table Type

CREATE TYPE dbo.OrderDetailsType AS TABLE 
(
	ItemID INT,
	Quantity INT,
	Price MONEY
)

Creating a table that uses the User Defined Table Type

CREATE PROCEDURE SaveOrder 
(
	@orderInfo OrderDetailsType READONLY
)
AS
BEGIN
	SELECT * FROM @orderInfo
	-- Do something with the data
END

Invoking the stored procedure and passing a User Defined Table Type value

DECLARE @ord OrderDetailsType
INSERT INTO @ord(ItemID, Quantity,Price)
	SELECT 1, 1, 10.00
INSERT INTO @ord(ItemID, Quantity,Price)
	SELECT 1, 1, 10.00
EXECUTE SaveOrder @ord