Export (0) Print
Expand All

Using User-Defined Tables

User-defined tables represent tabular information. They are used as parameters when you pass tabular data into stored procedures or user-defined functions. User-defined tables cannot be used to represent columns in a database table.

The Database object has a UserDefinedTableTypes property that references a UserDefinedTableTypeCollection object. Each UserDefinedTableType object in that collection has a Columns() property that refers to a collection of Column objects that list the columns in the user-defined table. Use the Add method to add columns to the user-defined table.

When you define a new user-defined table by using the UserDefinedTableType object, you will have to supply columns and a primary key based on one of the columns.

User-defined table types cannot be altered after they are created. The UserDefinedTableType does not support the Alter method. User-defined table types can have check constraints, but some check operations will throw an exception because the type is not alterable.

The Datatype class is used to specify the data type that is associated with columns and parameters. Use this type to specify the user-defined table type as a parameter for user-defined functions and stored procedures.

To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see "How to: Create a Visual Basic SMO Project in Visual Studio .NET" or "How to: Create a Visual C# SMO Project in Visual Studio .NET" in SQL Server Books Online.

For this example, you will have to include an imports statement for the class library that contains the StringCollection type.

Imports System.Collections.Specialized

The example demonstrates how to create a user-defined table, and then how to use it as a parameter in a user-defined function.

'Connect to the local, default instance of SQL Server
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a UserDefinedTableType object variable by supplying the 'database and name in the constructor.
Dim udtt As UserDefinedTableType
udtt = New UserDefinedTableType(db, "My_User_Defined_Table")
'Add three columns of different types to the
'UserDefinedTableType object.
udtt.Columns.Add(New Column(db, "Col1", DataType.Int))
udtt.Columns.Add(New Column(db, "Col2", DataType.VarCharMax))
udtt.Columns.Add(New Column(db, "Col3", DataType.Money))
'Define an Index object variable by supplying the user-defined
'table variable and name in the constructor.
Dim idx As Index
idx = New Index(udtt, "PK_UddtTable")
'Add the first column in the user-defined table as
'the indexed column.
idx.IndexedColumns.Add(New IndexedColumn(idx, "Col1"))
'Specify that the index is a clustered, unique, primary key.
idx.IsClustered = True
idx.IsUnique = True
idx.IndexKeyType = IndexKeyType.DriPrimaryKey
udtt.Indexes.Add(idx)
'Add the index and create the user-defined table.
udtt.Create()
'Display the Transact-SQL creation script for the
'user-defined table.
Dim sc As StringCollection
sc = udtt.Script()
Dim n As Integer
n = -1
Do While n < sc.Count
n = n + 1
Console.WriteLine(sc(n))
Loop
'Define a new user-defined function with a single parameter.
Dim udf As UserDefinedFunction
udf = New UserDefinedFunction(db, "My_User_Defined_Function")
udf.TextMode = False
udf.FunctionType = UserDefinedFunctionType.Inline
'Specify the parameter as a UserDefinedTableTable object.
Dim udfp As UserDefinedFunctionParameter
udfp = New UserDefinedFunctionParameter
udfp.DataType = New DataType(udtt)
udf.Parameters.Add(udfp)
'Specify the TextBody property to the Transact-SQL definition of the
'user-defined function.
udf.TextBody = "RETURNS TABLE AS (SELECT * from @param)"
'Create the user-defined function.
udf.Create()

For this example, you will have to include an imports statement for the class library that contains the StringCollection type.

using System.Collections.Specialized;

The example shows how to create a user-defined table, and then how to use it as a parameter in a user-defined function.

//Connect to the local, default instance of SQL Server 
{ 
   Server srv = default(Server); 
   srv = new Server(); 
   //Reference the AdventureWorks database. 
   Database db = default(Database); 
   db = srv.Databases("AdventureWorks"); 
   //Define a UserDefinedTableType object variable by supplying the
   //database and name in the constructor. 
   UserDefinedTableType udtt = default(UserDefinedTableType); 
   udtt = new UserDefinedTableType(db, "My_User_Defined_Table"); 
   //Add three columns of different types to the 
   //UserDefinedTableType object. 
   udtt.Columns.Add(new Column(db, "Col1", DataType.Int)); 
   udtt.Columns.Add(new Column(db, "Col2", DataType.VarCharMax)); 
   udtt.Columns.Add(new Column(db, "Col3", DataType.Money)); 
   //Define an Index object variable by supplying the user-defined 
   //table variable and name in the constructor. 
   Index idx = default(Index); 
   idx = new Index(udtt, "PK_UddtTable"); 
   //Add the first column in the user-defined table as 
   //the indexed column. 
   idx.IndexedColumns.Add(new IndexedColumn(idx, "Col1")); 
   //Specify that the index is a clustered, unique, primary key. 
   idx.IsClustered = true; 
   idx.IsUnique = true; 
   idx.IndexKeyType = IndexKeyType.DriPrimaryKey; 
   udtt.Indexes.Add(idx); 
   //Add the index and create the user-defined table. 
   udtt.Create(); 
   //Display the Transact-SQL creation script for the 
   //user-defined table. 
   StringCollection sc = default(StringCollection); 
   sc = udtt.Script(); 
   int n = 0; 
   n = -1; 
   while (n < sc.Count) { 
      n = n + 1; 
      Console.WriteLine(sc(n)); 
   } 
   //Define a new user-defined function with a single parameter. 
   UserDefinedFunction udf = default(UserDefinedFunction); 
   udf = new UserDefinedFunction(db, "My_User_Defined_Function"); 
   udf.TextMode = false; 
   udf.FunctionType = UserDefinedFunctionType.Inline; 
   //Specify the parameter as a UserDefinedTableTable object. 
   UserDefinedFunctionParameter udfp = default(UserDefinedFunctionParameter); 
   udfp = new UserDefinedFunctionParameter(); 
   udfp.DataType = new DataType(udtt); 
   udf.Parameters.Add(udfp); 
   //Specify the TextBody property to the Transact-SQL definition of the 
   //user-defined function. 
   udf.TextBody = "RETURNS TABLE AS (SELECT * from @param)"; 
   //Create the user-defined function. 
   udf.Create(); 
} 
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft