Table-Valued Parameters

SQL Server 2008 introduces a new parameter type called the table-valued parameters.  With the table-valued parameters, which are declared by using user-defined table types as will be described below, you can now send multiple rows of data to a function or stored procedure without creating a temporary table or many parameters. To illustrate the use of table-valued parameters in comparison to how stored procedures or functions are used prior to SQL Server 2005, let's say you are maintaining a table of contacts ([dbo].[Contact]) that contains the email address, first name and last name of your contacts.  You would create a stored procedure that accepts as separate parameters the email address, first name and last name.

CREATE TABLE [dbo].[Contact] (
    [Email]         VARCHAR(100),
    [FirstName]     VARCHAR(50),
    [LastName]      VARCHAR(50)
)
GO

CREATE PROCEDURE [dbo].[usp_ProcessContact]
    @Email      VARCHAR(100),
    @FirstName  VARCHAR(50),
    @LastName   VARCHAR(50)
AS

IF NOT EXISTS (SELECT 'X' FROM [dbo].[Contact]
               WHERE [Email] = @Email)
    INSERT INTO [dbo].[Contact] ( [Email], [FirstName], [LastName] )
    VALUES ( @Email, @FirstName, @LastName )
ELSE
    UPDATE [dbo].[Contact]
    SET [FirstName] = @FirstName,
        [LastName]  = @LastName
    WHERE [Email] = @Email
GO

What the stored procedure does is insert the new contact information if the email address does not exist in the [dbo].[Contact] table.  If the email address already exists, it updates the first name and last name of the contact.  To add or update contacts, you would have to call the stored procedure as many times as you have contacts.

EXECUTE [dbo].[usp_ProcessContact] 'mickey@mouse.com', 'Mickey', 'Mouse'
EXECUTE [dbo].[usp_ProcessContact] 'minnie@mouse.com', 'Minnie', 'Mouse'

SELECT * FROM [dbo].[Contact]

Email               FirstName    LastName
------------------  -----------  -----------
mickey@mouse.com    Mickey       Mouse
minnie@mouse.com    Minnie       Mouse

The Table-Valued Parameter Way

The stored procedure above can be modified to accept just one parameter, which is the table-valued parameter, instead of 3 different parameters for the email address, first name and last name.  The first step in making use of table-valued parameters is to create a user-defined table type.  A user-defined table type is a user-defined type that represents the definition of a table structure.  To create a user-defined table type, you will use the CREATE TYPE statement, followed by the table structure definition.

CREATE TYPE [ContactTemplate] AS TABLE (
    [Email]             VARCHAR(100),
    [FirstName]         VARCHAR(50),
    [LastName]          VARCHAR(50)
)
GO

The next step after creating the user-defined table type is to use that type as the parameter of the stored procedure.  In the case of the stored procedure above, we will be replacing the 3 parameters with just one parameter, the table-valued parameter. 

CREATE PROCEDURE [dbo].[usp_ProcessContact]
    @Email      VARCHAR(100),
    @FirstName  VARCHAR(50),
    @LastName   VARCHAR(50)

The parameters of the stored procedure now becomes like this:

CREATE PROCEDURE [dbo].[usp_ProcessContact]
    @Contact    ContactTemplate READONLY

One thing to note in this parameter is the READONLY attribute.  Table-valued parameters must be passed as input READONLY parameters to stored procedures or functions.  If you forget to include the READONLY attribute, you will get the following error message:

Msg 352, Level 15, State 1, Procedure usp_ProcessContact, Line 1
The table-valued parameter "@Contact" must be declared with the READONLY option.

One thing to remember also is that DML operations such as DELETE, INSERT or UPDATE on a table-valued parameter in the body of a stored procedure or function are not allowed.  If you try to issue a DELETE, INSERT or UPDATE statement into a table-valued parameter, you will get the following error message:

Msg 10700, Level 16, State 1, Procedure usp_ProcessContact, Line 6
The table-valued parameter "@Contact" is READONLY and cannot be modified.

The body of the stored procedure will also change since we are now processing multiple rows of contacts instead of one contact at a time.  The updated stored procedure, which now uses a table-valued parameter, will now look something like the following:

CREATE PROCEDURE [dbo].[usp_ProcessContact]
    @Contact    ContactTemplate READONLY
AS

-- Update First Name and Last Name for Existing Emails
UPDATE A
SET [FirstName] = B.[FirstName],
    [LastName]  = B.[LastName]
FROM [dbo].[Contact] A INNER JOIN @Contact B
  ON A.[Email] = B.[Email]

-- Add New Email Addresses
INSERT INTO [dbo].[Contact] ( [Email], [FirstName], [LastName] )
SELECT [Email], [FirstName], [LastName]
FROM @Contact A
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Contact] B
                  WHERE A.[Email] = B.[Email])
GO

To use this updated stored procedure, we first have to declare a local variable whose type is the user-defined table type we defined earlier and which is the type that the stored procedure is expecting.

DECLARE @Contacts ContactTemplate

Now we fill this table variable with the list of contacts we want to add or update in the [dbo].[Contact] table.

INSERT INTO @Contacts
VALUES ( 'mickey@mouse.com', 'Mickey', 'Mouse' ),
       ( 'minnie@mouse.com', 'Minnie', 'Mouse' )

The syntax of the INSERT statement shown here is the new way of inserting multiple values to a table using a single INSERT statement, which is introduced in SQL Server 2008.  For more details about this new feature, you can refer to the article entitledMultiple Value Inserts Using a Single INSERT Statement.

The last step is to pass the table variable to the stored procedure:

EXECUTE [dbo].[usp_ProcessContact] @Contacts

The complete script in calling the stored procedure that uses the table-valued parameter is as follows:

DECLARE @Contacts ContactTemplate

INSERT INTO @Contacts
VALUES ( 'mickey@mouse.com', 'Mickey', 'Mouse' ),
       ( 'minnie@mouse.com', 'Minnie', 'Mouse' )

EXECUTE [dbo].[usp_ProcessContact] @Contacts
SELECT * FROM [dbo].[Contact]

Email               FirstName    LastName
------------------  -----------  -----------
mickey@mouse.com    Mickey       Mouse
minnie@mouse.com    Minnie       Mouse