Click here to Skip to main content
15,121,505 members
Articles / Programming Languages / SQL
Posted 11 Jul 2010

Tagged as


12 bookmarked

SQL Server 2008 User Defined Table Types and Table-Valued Parameters

Rate me:
Please Sign up or sign in to vote.
4.90/5 (6 votes)
14 Jul 2010CPOL2 min read
Simple demonstration of using UDTTs and TVPs to pass DataTables to SQL Server 2008
SQL Server 2008 introduced the concepts of User-Defined Table Types (UDTTs) and Table-Valued Parameters (TVPs).
These allow you to pass a DataTable as a parameter.
There are a few articles about these here.
The articles I've seen pass DataTables to Stored Procedures, but you can use them in embedded SQL as well.

One of the simplest uses for a Table-Valued Parameter is with the use of the IN clause.
Many times a user may specify a set of values and you want to select some data based on those values.
The simple solution to this is to format an SQL statement with the provided values and execute it; this is a problematic technique (I won't list the reasons).
These problems are avoided by passing a DataTable that contains the values to use.

For the included demo, I created and populated a table that contains an ID (int) and a Name (varchar). I want to select the rows with particular IDs.

0) Define the User-Defined Table Type. I suggest you keep it simple and generic.
For my example, I just need a table of integers:
CREATE TYPE [dbo].[IntID] AS TABLE ( [ID] [int] NOT NULL )

I also created one that uses GUIDs:
CREATE TYPE [dbo].[GuidID] AS TABLE ( [ID] [uniqueidentifier] NOT NULL )

You may also define a User-Defined Table Type with multiple columns.
Ideally you could define the types in one database and access them from any other database on the server, but that is not currently supported.

1) Write the SQL to use it.

@IDs will be the Table-Valued Parameter, because it's a table, we can select from it.

2) When writing the code, simply add the DataTable as a parameter. The important step is to tell ADO.NET what type the parameter is:
cmd.Parameters.AddWithValue ( "@IDs" , dt ) ;
cmd.Parameters [ "@IDs" ].TypeName = "IntID" ;

3) Execute the command; ADO.NET and SQL Server will handle the rest.

The demo program supports three SQL statements that do similar things.
You choose a statement via a command-line parameter.

/*                                                                                                            */
/*  UDTTdemo.cs                                                                                               */
/*                                                                                                            */
/*  Demonstrates usage of a User Defined Table Type in SQL Server 2008                                        */
/*                                                                                                            */
/*  Modification history:                                                                                     */
/*  2010-07-11          Sir John E. Boucher     Created                                                       */
/*                                                                                                            */
namespace UDTTdemo
    public static class UDTTdemo
        /* Added bonus: use of enum and Dictionary rather than a switch on string */
        private enum Choice { Help , In , Exists , Join } ;
        private static readonly System.Collections.Generic.Dictionary<Choice,string> command ;
        static UDTTdemo
            command = new System.Collections.Generic.Dictionary<Choice,string>() ;
            command.Add ( Choice.Help   , "SELECT 'Syntax:' , 'UDTTdemo [ IN | EXISTS | JOIN ]'"                                   ) ;
            command.Add ( Choice.In     , "SELECT * FROM Account WHERE ID IN ( SELECT ID FROM @IDs )"                              ) ;
            command.Add ( Choice.Exists , "SELECT * FROM Account WHERE EXISTS ( SELECT ID FROM @IDs IDs WHERE IDs.ID=Account.ID )" ) ;
            command.Add ( Choice.Join   , "SELECT * FROM Account INNER JOIN @IDs IDs ON Account.ID=IDs.ID"                         ) ;
            return ;
        public static int
            string[] args
            int result = 0 ;
                /* Instantiate and populate a DataTable with the desired values */
                System.Data.DataTable dt = new System.Data.DataTable() ;
                dt.Columns.Add ( "ID" , typeof(int) ) ;
                dt.Rows.Add ( 5 ) ;
                dt.Rows.Add ( 3 ) ;
                dt.Rows.Add ( 1 ) ;
                /* Instantiate a Connection */
                    System.Data.SqlClient.SqlConnection con
                    new System.Data.SqlClient.SqlConnection
                    ( "Integrated Security=SSPI;Server=localhost\\sqlexpress;Database=Rubbish" )
                    /* Instantiate and setup a Command */
                    System.Data.SqlClient.SqlCommand cmd = con.CreateCommand() ;
                    Choice choice = Choice.Help ;
                    if ( args.Length > 0 )
                        /* System.Enum.TryParse<T> is new for .net 4 */
                        System.Enum.TryParse<Choice> ( args [ 0 ] , true , out choice ) ;
                    cmd.CommandText = command [ choice ] ;
                    /* Create and set the Table-Valued Parameter (TVP) */
                    cmd.Parameters.AddWithValue ( "@IDs" , dt ) ;
                    cmd.Parameters [ "@IDs" ].TypeName = "IntID" ;
                    /* Get and read a DataReader */
                    con.Open() ;
                    System.Data.IDataReader dr = cmd.ExecuteReader
                        ( System.Data.CommandBehavior.CloseConnection ) ;
                    while ( dr.Read() )
                        System.Console.WriteLine ( "{0} {1}" , dr [ 0 ] , dr [ 1 ] ) ;
                    /* Clean up */
                    dr.Close() ;
                    con.Close() ;
            catch ( System.Exception err )
                System.Console.WriteLine ( err ) ;
            return ( result ) ;

Compile it at the DOS prompt with csc UDTTdemo.cs (if you have added the .NET 4 directory to your path).


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior)
United States United States
BSCS 1992 Wentworth Institute of Technology

Originally from the Boston (MA) area. Lived in SoCal for a while. Now in the Phoenix (AZ) area.

OpenVMS enthusiast, ISO 8601 evangelist, photographer, opinionated SOB, acknowledged pedant and contrarian


"I would be looking for better tekkies, too. Yours are broken." -- Paul Pedant

"Using fewer technologies is better than using more." -- Rico Mariani

"Good code is its own best documentation. As you’re about to add a comment, ask yourself, ‘How can I improve the code so that this comment isn’t needed?’" -- Steve McConnell

"Every time you write a comment, you should grimace and feel the failure of your ability of expression." -- Unknown

"If you need help knowing what to think, let me know and I'll tell you." -- Jeffrey Snover [MSFT]

"Typing is no substitute for thinking." -- R.W. Hamming

"I find it appalling that you can become a programmer with less training than it takes to become a plumber." -- Bjarne Stroustrup

ZagNut’s Law: Arrogance is inversely proportional to ability.

"Well blow me sideways with a plastic marionette. I've just learned something new - and if I could award you a 100 for that post I would. Way to go you keyboard lovegod you." -- Pete O'Hanlon

"linq'ish" sounds like "inept" in German -- Andreas Gieriet

"Things would be different if I ran the zoo." -- Dr. Seuss

"Wrong is evil, and it must be defeated." –- Jeff Ello

"A good designer must rely on experience, on precise, logical thinking, and on pedantic exactness." -- Nigel Shaw

“It’s always easier to do it the hard way.” -- Blackhart

“If Unix wasn’t so bad that you can’t give it away, Bill Gates would never have succeeded in selling Windows.” -- Blackhart

"Use vertical and horizontal whitespace generously. Generally, all binary operators except '.' and '->' should be separated from their operands by blanks."

"Omit needless local variables." -- Strunk... had he taught programming

Comments and Discussions

GeneralReason for my vote of 5 Looks very interesting. Pin
KenJohnson23-Jul-10 22:49
MemberKenJohnson23-Jul-10 22:49 
Reason for my vote of 5
Looks very interesting.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.