TVP is used in the scenario where we need to pass a table data to Stored procedure. This was introduced in SQL Server 2008 and further version.
Following points to be followed when using TVP:
1. Create a Table type and define the table Structure.
2. Create the Stored Procedure by passing the Table Type parameters.
3. Declare the Table Type Variable
4. Insert the Table data to Table Type Variable.
5. Exec the Stored Procedure by passing Table Type Variable.
Create the Sample table:
IF OBJECT_ID('SalesHistory')>0
DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO
Step1 : Create a Table type and define the table Structure
CREATE TYPE SalesHistoryTableType AS TABLE
(
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GONOTE : check the system table if Table Type is created or not
SELECT * FROM sys.table_types
Step2 : Create the Stored Procedure by passing the Table valued parameters.
NOTE : When a Table Type is passed as parameter it should always be READONLY.
Cannot perform DML operation on Table valued Parameter
CREATE PROCEDURE usp_InsertBigScreenProducts
(
@TableVariable SalesHistoryTableType READONLY
)
AS
BEGIN
INSERT INTO SalesHistory
(
Product,
SaleDate, SalePrice
)
SELECT
Product, SaleDate,
SalePrice
FROM
@TableVariable
WHERE
Product = 'BigScreen'
END
GO
Step3 : Declare the Table Type Variable
DECLARE @DataTable AS SalesHistoryTableType
Step4 : Insert the Table data to Table Type Variable. Note that not exactly the below insert to be used (This is for example). Inserting the record into Table Type variable can be based on your scenario.
DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=1000)
BEGIN
INSERT INTO @DataTable(Product, SaleDate, SalePrice)
VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))
INSERT INTO @DataTable(Product, SaleDate, SalePrice)
VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))
INSERT INTO @DataTable(Product, SaleDate, SalePrice)
VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))
SET @i = @i + 1
END
Step5 : Exec the Stored Procedure by passing Table Type Variable.
EXECUTE usp_InsertBigScreenProducts @TableVariable = @DataTable