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
Pingback: How To Generate Multiple Rows In A Stored Procedure? - Programming Questions And Solutions Blog