Home » SQL SERVER User Defined Table Type and Table Valued Parameters

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,

.

)

Lets Look at an example , Creating a user defined table type

Creating a User defined table Type

Lets create a user Defined table type for Order Details table

CREATE TYPE UT_OrderDetails AS TABLE

(
Order_Id int NOT NULL,

OrderDesc  varchar(500),

OrderDate DateTime
)


we can check the User-Defined Tables Types on SQL server object explorer ,we find that UT_OrderDetails is created.

Now , Lets look at how to use user defined type table

Declare @OrderDetails as UT_OrderDetails

insert into @OrderDetails values(1,'Computer Compaq' ,getdate())

select * from @orderDetails
GO

Here we have used a table default table structure of User Defined type table that is order Details table .

 

So basically it is very helpful in such a case where we need any table , temp table or table variable just to perform some DML operations therefore its good idea to use table type rather than creating a new table again and again every time.

 

Use Table valued parameter in Stored Procedure

The following example , shows how to use user table valued parameter in a stored procedure.

As we have already created a user defined table type – UT_OrderDetails 

Now we create a stored procedures that uses a parameter of user defined table type  , which insert records into table and returns table records , as given below.

Create proc InsertOrderDetails (@orderdetails UT_OrderDetails)
as

begin

insert into OrderDetails (order_Id , orderDesc ,OrderDate)
select * from @orderdetails ;

end

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

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

Create proc InsertOrderDetails (@orderdetails UT_OrderDetails readonly)
as

begin

insert into OrderDetails (order_Id , orderDesc ,OrderDate)
select * from @orderdetails ;

end

 

Now we insert 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 create a table type variable @OrderDetails that reference to table type UT_OrderDetails . Now we insert some values into the variable then passed this variable to stored procedure .

Lets check OrderDetails table whether the record is inserted or not into table.

As we can see , record is inserted in 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

Lets check OrderDetails table whether the record is updated or not into table.

As we can see , record is updated.

Select * from OrderDetails

Delete records 

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

 

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

As we are using an existing user defined table type that have table structure of three columns so we have to pass three column value during the insertion values tovariable @orderDetails , otherwise to delete records from table we need only one unique value or we can use any user defined table type that have only single column.

Declare @OrderDetails as UT_OrderDetails

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

Exec delete_OrderDetails @OrderDetails

Lets check OrderDetails table whether the record is deleted or not into table.

As we can see , record is 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.

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.