Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Using Table Valued Parameters (TVP) in SQL Server 2008

MSSQLTips author Ray Barley By:   |   Read Comments (4)   |   Related Tips: More > Table Valued Parameters

Problem
As we are looking through the new features in SQL Server 2008 we found a potentially interesting one called Table-Valued Parameters.  Can you give us a detailed explanation of how we go about using this one?

Solution
Table-Valued Parameters are indeed a new feature in SQL Server 2008.  As the name implies, you can now pass a table type as a parameter to a function or stored procedure.  At a high level the TVP allows you to populate a table declared as a T-SQL variable, then pass that table as a parameter to a stored procedure or function.  The benefit of the TVP is that you can send multiple rows of data to the stored procedure or function rather than having to declare multiple parameters  or possibly use an XML parameter type to handle a variable number of rows.  According to Books on Line, a TVP is an efficient option for up to 1,000 or so rows.

In this tip we are going to gain an understanding of TVP by walking through a simple code sample to demonstrate how to:

  • Create a table type that can be passed as a TVP to a function or stored procedure
  • Create a stored procedure that uses a TVP
  • Declare the table type, populate it with data, and pass it to a stored procedure

Now let's describe our example.  In the extract, transform, and load (ETL) process in our data warehousing applications we typically map source system keys to surrogate keys during dimension processing; we then use the surrogate keys to uniquely identify the dimension rows in the warehouse.  This allows us to retain the complete history of dimension rows, as each change to a dimension row can be stored in a new row with a new surrogate key.  As dimension rows are changed or added, we simply assign a new surrogate key to the source system key and insert a new row into the dimension table.  For more details on surrogate key processing see our earlier tip Handling Slowly Changing Dimensions in SQL Server Integration Services (SSIS) Packages.  When processing fact rows we lookup the surrogate keys for the source system keys in the fact and store the surrogate key in the fact table; our queries join the fact table to the dimension table by the surrogate key.  Since multiple fact tables typically reference a given dimension (e.g. Customer), the  surrogate key lookup provides a good example for using TVP, allowing us to implement the surrogate key lookup one time in a stored procedure, then call it during our ETL process for multiple fact tables.

In addition to simply looking up the surrogate key for a source system key, we also have a situation where a fact table may have a source system key that doesn't exist in a dimension table.  In this case we want to create an inferred member in the dimension; i.e. create a new surrogate key and add it to the dimension then update it later when we get the actual dimension row from the source system.  For more details on inferred member processing for a dimension, take a look at our earlier tip Handling Early Arriving Facts in SQL Server Integration Services (SSIS) Packages.

The demo code below was only tested on the February, 2008 Community Technology Preview (CTP) of SQL Server 2008. 

Create a Table Type

In order to pass a table as parameter to a stored procedure or function, you first create a TABLE TYPE as follows:

CREATE TYPE SourceKeyList AS TABLE ( 
  SourceKey NVARCHAR(50)
)
GO

The T-SQL code is very similar to creating an ordinary table.  You can query sys.types in the current database to determine any table types that have been created:

SELECT name, system_type_id, user_type_id  
FROM sys.types
WHERE is_table_type = 1

Create a Stored Procedure with a TVP

We are going to create a stored procedure that performs the surrogate key lookup and adds an inferred member if the source key doesn't exist.  First we need to create a sample dimension table:

CREATE TABLE dbo.dim_Customer (
 sk_Customer  INT IDENTITY NOT NULL,
 CustomerSourceKey NVARCHAR(50) NOT NULL,
 CustomerName  NVARCHAR(50) NOT NULL,
 InferredMember  BIT NOT NULL
)

The surrogate key is an integer type and we use the IDENTITY property to automatically assign the next sequential number when inserting rows.  The InferredMember column gets set to 1 when we insert a row for a source key that doesn't exist.  When the row is extracted from the source system during dimension processing, the inferred row is updated with the columns from the source and the InferredMember column is set to 0.

Now let's create a stored procedure that takes our table type as a parameter and performs the surrogate key lookup and inferred processing:

CREATE PROCEDURE dbo.stp_GetCustomerSK
@source_key_list SourceKeyList READONLY
AS
BEGIN
 INSERT INTO dbo.dim_Customer(
 CustomerSourceKey, CustomerName, InferredMember
 )
 SELECT SourceKey, N'INFERRED', 1
 FROM @source_key_list k
 LEFT JOIN dbo.dim_Customer c ON c.CustomerSourceKey = k.SourceKey
 WHERE sk_Customer IS NULL
 
 SELECT sk_Customer, CustomerSourceKey
 FROM dbo.dim_Customer c
 JOIN @source_key_list k ON k.SourceKey = c.CustomerSourceKey
END
GO

The TVP must be declared READONLY.  You cannot perform any DML (i.e. INSERT, UPDATE, DELETE) against the TVP;  you can only reference it in a SELECT statement.  The stored procedure joins the TVP to the customer dimension to determine any source keys that do not already exist then inserts them.  The stored procedure then joins the TVP to the customer dimension to return a result set of the source key and their corresponding surrogate key.

You can query sys.parameters to view any parameters that have been declared READONLY:

SELECT object_id, name FROM sys.parameters
WHERE is_readonly = 1
GO

Declare TVP, Populate and Pass as a Parameter

You declare a T-SQL variable as a table type then populate it with rows by using an INSERT statement:

DECLARE @source_key_list SourceKeyList

INSERT INTO @source_key_list
SELECT 'CustomerID_001' UNION ALL
SELECT 'CustomerID_002' UNION ALL
SELECT 'CustomerID_003'

EXEC dbo.stp_GetCustomerSK @source_key_list
GO

For demonstration purposes the above SELECT statement just hard-codes values to insert for the source system keys; you would normally do a SELECT DISTINCT from your source system table to extract the list of source system keys on which you want to perform the surrogate key lookup.  The output from the above would look like this:

The output shows the surrogate key for each source key.

Next Steps

  • Download a copy of the latest Community Technology Preview of SQL Server 2008 from this site.  The above examples were created using the February, 2008 CTP.
  • Download a copy of the sample code here and experiment with TVP. 
  • Review the SQL Server 2008 Books on Line content for CREATE TYPE and Table-Valued Parameters for additional information.


Last Update: 4/23/2008


About the author
MSSQLTips author Ray Barley
Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, April 24, 2008 - 3:58:54 AM - CraneBoba Read The Tip

This is very intresting article

I wonder, is it possible to use this type from any client app, like VB.net

I mean, to fill some structure at client and send it to server in the way, server will undestand like TVP parameter

( ado object or array or what?)I tied to do it, and always got error - varchar is not comatible with table type


Thursday, April 24, 2008 - 5:55:54 PM - raybarley Read The Tip

 Yes.  Here are the links to Books on LIne:

ODBC - http://technet.microsoft.com/en-us/library/bb522663(SQL.100).aspx

OLEDB - http://technet.microsoft.com/en-us/library/bb510472(SQL.100).aspx

 


Friday, August 23, 2013 - 10:17:39 PM - Delwar Read The Tip

I've created the UDF below and trying to pass two tables as parameters. I've created datatype for tables already. function compiles fine but it shows the error as shown below:

..execute: select dbo.CompareTables(Table_A, Table_B)  Note: Table_A and B are physical tables with data

Error: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Table_A'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Table_B'.

.....................FUNCTION................................

Create FUNCTION CompareTables
    (@TableA AS [dbo].[TableX] readonly,
    @TableB As [dbo].[TableY] readonly)

RETURNS int

AS
BEGIN
   
    Return (SELECT 1 FROM @TableA A WHERE NOT EXISTS(SELECT * FROM @TableB B WHERE A.ID=B.ID AND A.VALUE1=B.VALUE1 AND A.VALUE2=B.VALUE2))

END


Saturday, August 24, 2013 - 5:55:02 PM - Ray Barley Read The Tip

I created an example based on what you have in your question; I don't get any errors.  Here it is:

CREATE TYPE [dbo].[TableX] AS TABLE (
  ID int,
  VALUE1 int,
  VALUE2 int
)
GO

CREATE TYPE [dbo].[TableY] AS TABLE (
  ID int,
  VALUE1 int,
  VALUE2 int
)
GO

Create FUNCTION CompareTables
    (@TableA AS [dbo].[TableX] readonly,
    @TableB As [dbo].[TableY] readonly)

RETURNS int

AS
BEGIN
  
    Return (SELECT 1 FROM @TableA A WHERE NOT EXISTS(SELECT * FROM @TableB B WHERE A.ID=B.ID AND A.VALUE1=B.VALUE1 AND
    A.VALUE2=B.VALUE2))

END
GO


declare @TableA dbo.TableX, @TableB dbo.TableY
insert @TableA values (1,2,3)
insert @TableB values (1,2,3)

select dbo.CompareTables(@TableA, @TableB)




Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.