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:
- 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.
