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 named UT_OrderDetails table.

CREATE TYPE UT_OrderDetails AS TABLE

(
Order_Id int NOT NULL,

OrderDesc  varchar(500),

OrderDate DateTime
)

You can also check the User-Defined tables types in SQL server object explorer, you can see a UT_OrderDetails is created inside User-Defined Tables Types folder.

Now, lets 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 .

 

You can see,  it is very helpful in such 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 will 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, and 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 parameter as 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 have created a table type variable @OrderDetails that reference to table type UT_OrderDetails, then 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 to variable @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.

 

 

 28,323 total views,  2 views today