SQL SERVER User Defined Table Type and Table Valued Parameters

The concept of User Defined table type and table valued parameters were introduced in SQL server 2008.

A user-defined table type is a user-defined type that represents the definition of a table structure.

We can use user-defined table type to declare table-valued parameters for stored procedures or functions, or to declare table variables that we want to use in a batch or in the body of a stored procedure or function.

 Syntax

Create Type tablename as Table
( 
col1 datatype1,
col2 datatype2,
.
.
)

Let’s explore an example of creating a user-defined table type.

Creating a User defined table Type

Let’s create a user-defined table type called UT_OrderDetails

CREATE TYPE UT_OrderDetails AS TABLE
(
Order_Id int NOT NULL,
OrderDesc  varchar(500),
OrderDate DateTime
)

You can also view user-defined table types in the SQL Server Object Explorer. You will find UT_OrderDetails created within the User-Defined Table Types folder.

Now, let’s look at how to use user defined type table in SQL Server.

Declare @OrderDetails as UT_OrderDetails
insert into @OrderDetails values(1,'Computer Compaq', getdate())
select * from @orderDetails
GO

You can see, we have used a default table structure of User Defined type table that is order Details table.

 

It is particularly useful in situations where we need a table, temporary table, or table variable solely for performing DML operations.

Therefore, using a user-defined table type is a good idea, as it eliminates the need to create a new table repeatedly.

Use Table valued parameter in Stored Procedure

The following example demonstrates how to use a table-valued parameter in a stored procedure.

Having already created the user-defined table type UT_OrderDetails, Now we will create a stored procedures that uses a parameter of user defined table type. This stored procedure will insert records into the table and return the table records, as shown below.

Create proc InsertOrderDetails (@orderdetails UT_OrderDetails)
as
begin
insert into OrderDetails (order_Id, orderDesc, OrderDate)
select * from @orderdetails ;
end

When we tried to execute the above scripts, you can see it gives an error of Read Only option, that means Table-valued parameters must be passed as READONLY parameters and we can not perform DML operations like INSERT, UPDATE, DELETE  either, or on a table-valued parameter in the body of a routine.

Now we modify the above script and set the parameter to readonly.

Create proc InsertOrderDetails (@orderdetails UT_OrderDetails readonly)
as
begin
insert into OrderDetails (order_Id, orderDesc, OrderDate)
select * from @orderdetails ;
end

Now insert a record into OrderDetails table using stored procedures.

Declare @OrderDetails as UT_OrderDetails
insert into @OrderDetails values(1,'Computer Compaq', getdate())
Exec InsertOrderDetails @OrderDetails

 

Here, we created a table type variable @OrderDetails that references the UT_OrderDetails table type. We then inserted some values into this variable and passed this variable to the stored procedure.

Now, let’s check the OrderDetails table to see if the record has been inserted.

As we can see, the record has been successfully inserted into the table

Select * from OrderDetails

 

Update records 

To update the records using user-defined table types. Firstly, we create a procedure that updates the record.

Create proc UpdateOrderDetails (@orderdetails UT_OrderDetails readonly)
as
begin

Update OrderDetails
SET OrderDesc = o.orderDesc, Orderdate = o.OrderDate from
(select Order_Id, orderDesc, OrderDate from @orderdetails)O 
where
o.Order_Id =OrderDetails.Order_Id

end

 

Now we create a table type variable and pass this variable to stored procedure as user-defined table type parameter.

Declare @OrderDetails as UT_OrderDetails
insert into @OrderDetails
values(1,'Compaq HD Graphics laptops', getdate())

Exec UpdateOrderDetails @OrderDetails

Let’s check the OrderDetails table to determine whether the record has been updated.

As we can see, the record has been successfully updated.

Select * from OrderDetails

Delete records 

To delete the records using user-defined table types. Firstly, we create a procedure that deletes the record.

Create proc delete_OrderDetails(@orderdetails UT_OrderDetails readonly)

as

begin

delete from OrderDetails Where Order_Id  in ( 

select Order_Id from @orderdetails)

end

Now, we will create a table type variable and pass it to the stored procedure as a user-defined table type parameter.

Since we are using an existing user-defined table type that has a structure of three columns, we need to provide values for all three columns when inserting records into the variable @OrderDetails. However, to delete records from the table, we only require one unique value. Alternatively, we could use any user-defined table type that contains only a single column.

Declare @OrderDetails as UT_OrderDetails
insert into @OrderDetails values(1,'Compaq HD Graphics laptops', getdate())

Exec delete_OrderDetails @OrderDetails

Let’s check the OrderDetails table to see if the record has been deleted.

As we can see, the record has been successfully deleted

 

Restrictions with User Defined Table Type

• ALTER TABLE statements can not be used ,to modify the design of table-valued parameters. The only single way to change the table definition is to drop the type first and recreate the table type again.
• Table Valued Parameter can’t used as OUTPUT parameter in stored procedures.
• SQL Server does not maintain statistics on columns of table-valued parameters.
• We cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.