Monday, 3 February 2020

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.


Popular Posts