User Defined Table Types and Table Valued Parameters in sql server

Table-Valued Parameters is a new feature introduced in SQL SERVER 2008. In earlier versions of SQL SERVER, it is not possible to pass a table variable in a stored procedure as a parameter, but now in SQL SERVER 2008, we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.

Table-valued parameters (TVPs) are a great way to move chunks of data between our application and SQL Server, while still retaining the ability to abstract database functionality with stored procedures and functions.

Let’s see a simple example where we use the user-defined table types and table-valued parameters.

I am taking the AdventureWorks database for this demo.

We have below table and data product category, product subcategory and product details.

·         ProductCategory

·         ProductSubcategory

·         Product

 

There is two way to insert these records into the table. Either we can insert this records one by one or we can insert these records in one shot. We can use a user-defined table to insert it into one shot.

Let’s see how to create user defined table type

Syntax of the user-defined table type.

CREATE TYPE user_defined_table_name AS TABLE (                 --List of columns )

 See example

CREATE TYPE udt_Product_sub_category AS TABLE ( ProductCategoryID int, Name varchar(50) ) CREATE TYPE udt_Product AS TABLE ( ProductSubcategoryID int, Name varchar(50), ProductNumber varchar(20), StandardCost decimal(18,10), ListPrice decimal(18,10) )

We can see these on below

    

DatabaseàProgrammabilityàTypeà User-Defined Table Type.

Now I am creating an SP to insert the records into these three tables.

CREATE PROCEDURE p_Insert_Product_details (     @Product_Category varchar(50),     @Product_sub_category  udt_Product_sub_category READONLY,                 @product udt_Product READONLY ) AS BEGIN    INSERT INTO Production.ProductCategory (name) values (@Product_Category);       INSERT INTO Production.ProductSubcategory (ProductCategoryID,Name)    select ProductCategoryID,Name from @Product_sub_category;    INSERT INTO Production.Product (                                 ProductSubcategoryID,                                 Name,                                 ProductNumber,                                 StandardCost,                                 ListPrice)                 select                 ProductSubcategoryID,                                 Name,                                 ProductNumber,                                 StandardCost,                                 ListPrice                                 from @product udt_Product END;

SP is created successfully.

Now we need to execute this SP.

Declare @Product_Sub_category_Details as udt_Product_sub_category; Declare @Product_Details as udt_Product Declare @Product_category varchar(50); set @Product_category='Electronics'; --Inserting the records into the Product Sub category insert into @Product_Sub_category_Details (ProductCategoryID,Name) select 9,'Mobile' union all select 9,'Mobile Accessories' union all select 9,'Televesion' union all select 9,'Laptop' union all select 9,'Tablets' union all select 9,'Laptop Accessories' --Inserting the records into the Product insert into @Product_Details (ProductSubcategoryID,Name,ProductNumber,StandardCost,ListPrice,SafetyStockLevel) Select 54,'Mi','P1',10000,10000,1  union all Select 54,'Vivo','p2',20000,20000,1 union all Select 54,'Nokia','p3',3000,3000,1 union all Select 54,'Oppo','p4',40000,4000,1 union all Select 54,'Apple','p5',5000,5000,1 union all Select 54,'Samsung','p6',60000,6000,1 execute p_Insert_Product_details @Product_category,@Product_Sub_category_Details,@Product_Details

SP executed successfully.

  

See the records into the table

  

Important points to remember:

•  Table-valued parameters must be passed as READONLY parameters to SQL routines. You cannot perform DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
• You cannot use a table-valued parameter as the 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.
• We can’t drop the user-defined table type if it is referred to in any SP or function. First, we need to remove the reference and after that we and drop it.