Table Value Parameter (TVP) in SQL Server
This article summarize the use of Table Value Parameter (TVP) in stored procedures for DML operations. This list the advantages over the traditional way of passing multiple individual parameter as input.
Definition:
Table value parameters is one of method to pass collection of rows as input to Stored procedure for processing. For using TVP in a Stored procedure, we need to create TVP as inbuilt Table type and use it in Stored procedure.
Example:
I have considered an example of inserting multiple rows of value to Existing UserDetails table.
Table Structure
User details (Username, First Name,Last Name, Email, Active) is stored in UserDetails Table and Role Information is stored in UserRole Table with Role, Begin date and Term Date
Create Table UserDetails
(
UserID int identity(1,1) primary key,
UserName varchar(50),
FirstName Varchar(50),
LastName Varchar(50),
Email Varchar(100),
IsActive bit)
Create Table UserRole
(UserRolePK int identity(1,1),
UserID int FOREIGN KEY REFERENCES UserDetails(UserID),
UserRole varchar(30),
BeginDate datetime, TermDate datetime)
Creation of TVP
User defined Table type has to be created for using the TVP as shown below
Create Type TVPUserDetails as table
(
UserID int,
UserName varchar(50),
FirstName Varchar(50),
LastName Varchar(50),
Email Varchar(100),
UserRole Varchar(30),
IsActive bit )
Usage of TVP in SP
TVP is used as normal table inside SP.
Insert SP
--Insert
Create procedure DNS_INSUserDetails
(
@tvpUserDetails TVPUserDetails readonly
)
as
Begin
declare @UserID int
If not exists( Select UD.UserName from UserDetails UD, @tvpUserDetails TVPUD
where UD.UserName = TVPUD.UserName) ---(1)
Begin
Insert UserDetails (UserName, FirstName, LastName, Email, IsActive)
Select UserName, FirstName, LastName, Email, IsActive from @tvpUserDetails ---(2)
set @UserID = @@IDENTITY
Insert UserRole(UserID, UserRole, BeginDate, TermDate)
Select @UserID, UserRole, Getdate(), '2050-12-31' from @tvpUserDetails ---(3)
End
End
Note: TVP is used in 3 different places in the above code sample
Update SP
--Update
Create procedure DNS_UPDUserDetails
(
@tvpUserDetails TVPUserDetails readonly
)
as
Begin
declare @UserID int
If exists( Select UD.UserName from UserDetails UD, @tvpUserDetails TVPUD
where UD.UserName = TVPUD.UserName) ---(1)
Begin
Update UserDetails
Set FirstName = TVPUD.FirstName,
LastName = TVPUD.LastName,
Email = TVPUD.Email, IsActive = TVPUD.IsActive
From @tvpUserDetails TVPUD
Where UserDetails.UserID = TVPUD.UserID ---(2)
End
If exists( Select UR.UserID from UserRole UR, @tvpUserDetails TVPUD
where UR.UserID = TVPUD.UserID) ---(3)
Begin
Update UserRole
Set
TermDate = GETDATE()
From @tvpUserDetails TVPUD
Where UserRole.UserID = TVPUD.UserID ---(4)
End
Insert UserRole(UserID, UserRole, BeginDate, TermDate)
Select UserID, UserRole, Getdate(), '2050-12-31' from @tvpUserDetails ---(5)
End
Note: TVP is used in 5 different places in the above code sample(Update SP)
Execution of SP
go
-- Insert Query
Declare @tvpUserDetails TVPUserDetails
Insert @tvpUserDetails values(0,'Mahe','Mahe','M','Mahe@DNS.com','Developer',1)
Exec DNS_INSUserDetails @tvpUserDetails
go
-- Update Query
Declare @tvpUserDetails TVPUserDetails
Insert @tvpUserDetails values(6,'Mahe','Mahe','M','MaheM@DNS.com','Admin',1)
Exec DNS_UPDUserDetails @tvpUserDetails
Limiations
Note: To overcome this limitation, we can create a temporary table inside the SP and use it for the required manipulation