Click here to Skip to main content
Click here to Skip to main content

Tagged as

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

, 14 Jul 2010
Rate this:
Please Sign up or sign in to vote.
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.
SELECT * FROM Account WHERE ID IN ( SELECT ID FROM @IDs )"
@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 ;
        }
        [System.STAThreadAttribute()]
        public static int
        Main
        (
            string[] args
        )
        {
            int result = 0 ;
            try
            {
                /* 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 */
                using
                (
                    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).

License

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

Share

About the Author

PIEBALDconsult
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
 
---------------
 
"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

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

 
"We learn more from our mistakes than we do from getting it right the first time."
 
My first rule of debugging: "If you get a different error message, you're making progress."
 
My golden rule of database management: "Do not unto others' databases as you would not have done unto yours."
 
My general rule of software development: "Design should be top-down, but implementation should be bottom-up."

Comments and Discussions

 
GeneralReason for my vote of 5 Looks very interesting. PinmemberKenJohnson23-Jul-10 21:49 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 14 Jul 2010
Article Copyright 2010 by PIEBALDconsult
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid