Insert multiple rows to a table by User-Defined Table Types

   User-Defined Table type has been an option in SQL Server for some time now. You can find them under the Types folder in Management Studio.

If you need to pass multiple rows to a stored procedure, This type can help you to accomplish this.

The idea behind them is, creating a pre-defined temp table and pass it around as parameters or variables.

Before I give you an example, let's talk about what the limitations are.

• You can't use this type as a column in a table.
• You can't alter this type, you need to drop it and create it again.
• They are faster than creating a temp table to pass multiple values.

Okay, so let's say we have an online store. Users pick items and add them to their shopping carts, users can pick multiple items and add them to shopping carts. We need to insert the multiple items to our table by using User-Defined table type.

Our shopping cart table looks like this

To insert multiple rows in this table, first we need to create the User-Defined Table type.

You should be able to see the new type under Programmability >Types > User-Defined Table types

No we can use this type to insert multiple rows to our Shopping Cart. I am going to create a stored procedure to do that.

READONLY option is required because we can not change User-Defined Table type values.

Everything looks good so far in SQL side, but what about the application side? We need to pass UT_ShoppingCart type to the stored procedure in some way.

Since UT_ShoppingCart is a table type, we can use the DataTable object in C#

We should be good now, we need to add this parameter to our SqlCommand object and call our stored procedure.

If you are using Entity Framework, you might need to get EntityFrameworkExtras to use User-Defined Table types.