SQL - User Defined Table Types

So a very interesting feature in SQL Server 2008 is user-defined table types. This is basically a custom variable type. Similary to a typedef in C. You can create user-defined table type as so...


create type CustomerUdt as table
(
Id int,
CustomerName nvarchar(50),
PostalCode nvarchar(50)
)


and then utilize that in a stored procedure like so...


create procedure InsertCustomerInfo
(
@CustomerInfoTvp as CustomerUdt readonly
)
as
insert into [Customers]
select * from @CustomerInfoTvp


this will do a bulk insertion for you... very nice!

the schema that you setup in the user-defined table type has to be the same as [Customers] in the example above.

This works best in the case where you are make multiple calls to the database for each row of data to separate tables. You can combine user-defined table types in one stored procedure like so...


create procedure InsertNewOrder
(
@OrderTvp as OrderUdt readonly,
@OrderDetailTvp as OrderDetailsUdt readonly
)
insert into [Orders]
select * from @OrderTvp
insert into [OrderDetails]
select * from @OrderDetailTvp


That makes one call to the database. Easy peasy.