Introduction

This is the fourth post in a series of five on Using TVP’s and XML to Transport Relational Data.

In this post we’ll take a look at how Table-Valued Parameters (TVP’s) can be used to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure. We’ll also look at how .Net Developers can use TVP’s to reduce the number of SQL Server calls required to handle data modifications.

For details of other posts in this series, please refer to Using TVP’s and XML to Transport Relational Data.

Brief History

Table-valued parameters are declared by using user-defined table types and were introduced in SQL Server 2008.

Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQL. Table-valued parameters also have the benefit of being able to participate in set-based operations.

Restrictions

Table-valued parameters have the following restrictions:

• SQL Server does not maintain statistics on columns of table-valued parameters.
• Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
• You cannot use a table-valued parameter as 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.

Creating Tables with .Net

In Part 2 – OpenXML: What is it and how do I use it? I created an ADO.NET Dataset (dsPassenger) consisting of two tables; Passenger and PAX. I will use these same tables to pass data to a stored procedure that accepts Table-valued Parameters.

Creating and Using Table-Valued Parameters

    CREATE TYPE [Person].[udtt_Passenger] AS TABLE
(
	[Newsletter] [bit] NOT NULL,
	[PersonID] [uniqueidentifier] NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	PRIMARY KEY CLUSTERED 
	(
		[PersonID] ASC
	)
)
GO

CREATE TYPE [Person].[udtt_PAX] AS TABLE
(
	[PaxID] [uniqueidentifier] NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[PaxOrder] [tinyint] NOT NULL,
	PRIMARY KEY CLUSTERED 
	(
		[PaxID] ASC	)
)
GO

    CREATE PROCEDURE [Person].[usp_UpdatePassengerDetailTVP]
(
	@Passenger [Person].[udtt_Passenger] READONLY,
	@Pax [Person].[udtt_Pax] READONLY
)
AS 

SET NOCOUNT ON

UPDATE	[Person].[Detail]
SET		FirstName = P.FirstName,LastName = P.LastName,
		Newsletter = P.Newsletter
FROM	@Passenger P
WHERE	[Person].[Detail].PersonID = P.PersonID

UPDATE	[Person].[Pax]
SET		FirstName = P.FirstName,LastName = P.LastName,
		PaxOrder = P.PaxOrder
FROM	@Pax P
WHERE	[Person].[Pax].PAXID = P.PAXID

IF @@ERROR <> 0
    -- Return 99 to the calling program to indicate failure.
    RETURN 99
ELSE
    -- Return 0 to the calling program to indicate success.
    RETURN 0
    
SET NOCOUNT ON

-- Get current record details
SELECT	PersonID,FirstName,LastName,Newsletter
FROM	[Person].[Detail]
WHERE	PersonID = '7a7c18a0-ad47-e011-9c71-00155d991421'
  
SELECT	PaxID,FirstName,LastName,PaxOrder
FROM	[Person].[PAX]
WHERE	PaxID IN ('868b8fde-068a-4468-b9b6-9660166e630d',
		'bb2770e0-b0e0-479f-8c03-1330ce87cb64')
		
-- Create local table type variables and populate
-- with data
DECLARE @Passenger [Person].[udtt_Passenger]
DECLARE @Pax [Person].[udtt_Pax]

INSERT INTO @Passenger (PersonID,FirstName,LastName,Newsletter)
VALUES ('7a7c18a0-ad47-e011-9c71-00155d991421','Jeff','Wharton',0)

INSERT INTO @Pax (PaxID,FirstName,LastName,PaxOrder)
VALUES ('868b8fde-068a-4468-b9b6-9660166e630d','Lily','Wharton',3),
('bb2770e0-b0e0-479f-8c03-1330ce87cb64','Ben','Wharton',2)

-- Execute the stored procedure
EXEC [Person].[usp_UpdatePassengerDetailTVP] @Passenger, @PAX

-- Get updated record details
SELECT	PersonID,FirstName,LastName,Newsletter
FROM	[Person].[Detail]
WHERE	PersonID = '7a7c18a0-ad47-e011-9c71-00155d991421'
  
SELECT	PaxID,FirstName,LastName,PaxOrder
FROM	[Person].[PAX]
WHERE	PaxID IN ('868b8fde-068a-4468-b9b6-9660166e630d',
		'bb2770e0-b0e0-479f-8c03-1330ce87cb64')

Using .NET to Pass Table-Valued Parameters

The .Net code I need to use to call the stored procedure is practically the same as discussed in Part 2 – OpenXML: What is it and how do I use it?. All I need to do is is change the stored procedure name to [Person].[usp_UpdatePassengerDetailTVP] and replace the existing XML parameter with two new parameters of type SqlDbType.Structured:

db.AddInParameter(dbCommand, "@Passenger", SqlDbType.Structured, 
    dsPassengers.PassengerDataTable);
db.AddInParameter(dbCommand, "@Pax", SqlDbType.Structured, 
    dsPassengers.PaxDataTable);

Conclusion

As you can see from the above example, using Table-valued Parameters to send multiple rows of data to SQL Server is very simple to implement. That said, there are a couple of issues that make using TVP’s a bit of a pain (if not a waste of time):

• Once you’ve created a user-defined table types you can’t modify it. You need to drop and recreate.
• You can’t drop a user-defined table types if it’s being referenced by another object i.e. in this example, I cannot drop my UDDT’s as they are being referenced by stored procedure [Person].[usp_UpdatePassengerDetailTVP], thus I can not modify the UDDT’s unless I remove all references
• The order of columns in a Datatable need to be in the exact same order as the UDDT as no column mapping is applied.