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 AdventureWorks2012 database.
        Dim db As Database
        db = srv.Databases("AdventureWorks2012")
        '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(udtt, "Col1", DataType.Int))
        udtt.Columns.Add(New Column(udtt, "Col2", DataType.VarCharMax))
        udtt.Columns.Add(New Column(udtt, "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()
        For Each c As String In sc
            Console.WriteLine(c)
        Next

        '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.Scalar
        udf.ImplementationType = ImplementationType.TransactSql
        udf.DataType = DataType.DateTime
        'Specify the parameter as a UserDefinedTableTable object.
        Dim udfp As UserDefinedFunctionParameter
        udfp = New UserDefinedFunctionParameter(udf, "@param")
        udfp.DataType = New DataType(udtt)
        udfp.IsReadOnly = True
        udf.Parameters.Add(udfp)
        'Specify the TextBody property to the Transact-SQL definition of the
        'user-defined function.
        udf.TextBody = "BEGIN RETURN (GETDATE());end"
        '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 = new Server();

            //Reference the AdventureWorks2012 database. 
            Database db = srv.Databases["AdventureWorks2012"];
            //Define a UserDefinedTableType object variable by supplying the
            //database and name in the constructor. 
         UserDefinedTableType udtt = new UserDefinedTableType(db, "My_User_Defined_Table");

            //Add three columns of different types to the 
            //UserDefinedTableType object. 
         udtt.Columns.Add(new Column(udtt, "Col1", DataType.Int));
         udtt.Columns.Add(new Column(udtt, "Col2", DataType.VarCharMax));
         udtt.Columns.Add(new Column(udtt, "Col3", DataType.Money));

            //Define an Index object variable by supplying the user-defined 
            //table variable and name in the constructor. 
          
            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. 
            System.Collections.Specialized.StringCollection sc;
            sc = udtt.Script();
            foreach (string s in sc)
            {
                Console.WriteLine(s);
            }

            //Define a new user-defined function with a single parameter.
            UserDefinedFunction udf = new UserDefinedFunction(db, "My_User_Defined_Function");
            udf.TextMode = false;
            udf.FunctionType = UserDefinedFunctionType.Scalar;
            udf.ImplementationType = ImplementationType.TransactSql;
            udf.DataType = DataType.DateTime;

            //Specify the parameter as a UserDefinedTableTable object.
             UserDefinedFunctionParameter udfp = new UserDefinedFunctionParameter(udf, "@param");
            udfp.DataType = new DataType(udtt);
            udfp.IsReadOnly = true;
            udf.Parameters.Add(udfp);

            //Specify the TextBody property to the Transact-SQL definition of the 
            //user-defined function. 
            udf.TextBody = "BEGIN RETURN (GETDATE());end";
            //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.

# Set the path context to the local, default instance of SQL Server and get a reference to AdventureWorks2012
CD \sql\localhost\default\databases
$db = get-item Adventureworks2012

#Define a UserDefinedTableType object variable by supplying the
#database and name in the constructor. 
$udtt = New-Object -TypeName Microsoft.SqlServer.Management.SMO.UserDefinedTableType `
-argumentlist $db, "My_User_Defined_Table"

#Add three columns of different types to the UserDefinedTableType object.

$type = [Microsoft.SqlServer.Management.SMO.DataType]::Int
$col = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column `
-argumentlist $udtt, "col1",$type
$udtt.Columns.Add($col)

$type = [Microsoft.SqlServer.Management.SMO.DataType]::VarCharMax
$col = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column `
-argumentlist $udtt, "col2",$type
$udtt.Columns.Add($col)

 $type = [Microsoft.SqlServer.Management.SMO.DataType]::Money
$col = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column `
-argumentlist $udtt, "col3",$type
$udtt.Columns.Add($col)        
 
#Define an Index object variable by supplying the user-defined 
#table variable and name in the constructor. 
$idx = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index `
-argumentlist $udtt, "PK_UddtTable"

#Add the first column in the user-defined table as 
#the indexed column. 
$idxcol = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn `
-argumentlist $idx, "Col1"
$idx.IndexedColumns.Add($idxcol)

#Specify that the index is a clustered, unique, primary key. 
$idx.IsClustered = $true
$idx.IsUnique = $true
$idx.IndexKeyType = [Microsoft.SqlServer.Management.SMO.IndexKeyType]::DriPrimaryKey;

#Add the index and create the user-defined table. 
$udtt.Indexes.Add($idx)
$udtt.Create();

# Display the Transact-SQL creation script for the 
# user-defined table. 
$sc = $udtt.Script()
$sc

# Define a new user-defined function with a single parameter. 
$udf = New-Object -TypeName Microsoft.SqlServer.Management.SMO.UserDefinedFunction `
-argumentlist $db, "My_User_Defined_Function"
$udf.TextMode = $false
$udf.FunctionType = [Microsoft.SqlServer.Management.SMO.UserDefinedFunctionType]::Scalar
$udf.ImplementationType = [Microsoft.SqlServer.Management.SMO.ImplementationType]::TransactSql
$udf.DataType = [Microsoft.SqlServer.Management.SMO.DataType]::DateTime

# Specify the parameter as a UserDefinedTableTable object.
$udfp = New-Object -TypeName Microsoft.SqlServer.Management.SMO.UserDefinedFunctionParameter `
-argumentlist $udf, "@param"
$type    =  New-Object -TypeName Microsoft.SqlServer.Management.SMO.DataType `
-argumentlist $udtt
$udfp.DataType = $type
$udfp.IsReadOnly = $true
$udf.Parameters.Add($udfp)

# Specify the TextBody property to the Transact-SQL definition of the 
# user-defined function. 
$udf.TextBody = "BEGIN RETURN (GETDATE());end"

# Create the user-defined function. 
$udf.Create()         

Community Additions

ADD
Show:
© 2014 Microsoft