mssql2008中自定义表类型的例子

--======自定义表类型Demo======
--创建表
create table tab
(
 tabvalue varchar(10)
)
go
--创建自定义表类型
create type Type_tab as table
(
    Type_tabvalue varchar(10)
)
go
--创建 存储过程
create procedure p_Tab
(
@ptab Type_tab readonly
)
as
begin
    insert into tab select * from @ptab
end
--sql 测试表类型 type1
declare @tt Type_tab
insert into @tt values('a')
insert into @tt values('b')
insert into @tt values('c')
insert into tab select * from @tt
select * from tab
--sql 测试表类型 type2
declare @tt2 Type_tab
insert into @tt2 values('a')
insert into @tt2 values('b')
insert into @tt2 values('c')
execute p_Tab @tt2

 using (SqlConnection conn =SqlHelper.Settings.GetSqlConnection(true))
           {
               try
               {
                   DataTable DtAdd = new DataTable();
                   DtAdd.Columns.Add("tabvalue");               
                   DtAdd.Columns["tabvalue"].DataType = System.Type.GetType("System.String");
                   foreach (ModelEnt rl in List)
                   {
                       DataRow Dr = DtAdd.NewRow();
                       Dr["tabvalue"] = rl.tabvalue;                    
                       DtAdd.Rows.Add(Dr);
                   }
                   //int Rt = SqlHelper.SqlHelper.SqlBulkCopy(DtAdd, TableName, conn);

                   SqlCommand cmd = new SqlCommand("p_Tab", conn);
                   cmd.CommandType = CommandType.StoredProcedure;                
                   cmd.Parameters.Add("@ptab", SqlDbType.Structured).Value =DtAdd;
                 
                   if (conn.State == ConnectionState.Closed)
                   {
                       conn.Open();
                   }
                   return cmd.ExecuteNonQuery();
               }
               catch (Exception ex)
               {
#if DEBUG
                   throw ex;
#else
                   return -10000;
#endif
               }
               finally
               {
                   conn.Close();
               }