表值变量 在批量操作中的用法

表值变量在批量操作中的用法：

以更新地区的排序值为例子：

创建表值变量的方法：

CREATE TYPE Ty_UpdateAreaInfoShowOrder as Table
(
[ID] int primary key not null,
[ShowOrder] int null
)

在存储过程中做批量操作的例子：

CREATE PROC USP_UpdateAreaInfoShowOrder
@dt Ty_UpdateAreaInfoShowOrder readonly
AS
    BEGIN
    DECLARE @ERRORSUM SMALLINT =0
        BEGIN TRAN
            SET @ERRORSUM=-1;
        UPDATE  m SET M.ShowOrder = d.ShowOrder  FROM MD_AreaInfo m ,@dt d where m.ID = d.ID
            IF(@@ERROR<>0) GOTO ERROR_HANDLE;
            COMMIT TRAN;
            RETURN 0
    ERROR_HANDLE:
    ROLLBACK TRAN;
    RETURN @ERRORSUM;
    END

可以看到存储过程的传入参数的表值变量类型（上面所定义的表值变量）

调用存储过程的方法：

 public void BindGrid()
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT [ID], [CnName],[EnName],[ShowOrder] FROM MD_AreaInfo where Parentid=0 ORDER BY SHOWORDER ";
                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(ds);
                    this.gv.DataSource = ds.Tables[0];
                    this.gv.DataBind();
                }
            }
        }
        public DataTable CreateTable()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("ShowOrder", typeof(int));
            return dt;
        }
        protected void btn_Click(object sender, EventArgs e)
        {
            DataTable dt = CreateTable();
            for (int i = 0; i < gv.Rows.Count; i++)
            {
                HiddenField hd = (HiddenField)gv.Rows[i].FindControl("hdID");
                TextBox tb = (TextBox)gv.Rows[i].FindControl("txtShowOrder");
                DataRow dr = dt.NewRow();
                dr[0] = int.Parse(hd.Value);
                dr[1] = int.Parse(tb.Text.Trim());
                dt.Rows.Add(dr);
            }
            RunStore("USP_UpdateAreaInfoShowOrder", dt);
            BindGrid();
        }
        public int RunStore(string storeName, DataTable dt)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = storeName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter sp = new SqlParameter("@dt", SqlDbType.Structured);
                    sp.TypeName = "Ty_UpdateAreaInfoShowOrder";
                    sp.Value = dt;
                    cmd.Parameters.Add(sp);
                    return cmd.ExecuteNonQuery();
                }
            }
            return 0;
        }

表值参数是 SQL Server 2008 中的新参数类型。表值参数是使用用户定义的表类型来声明的。使用表值参数，可以不必创建临时表或许多参数，即可向 Transact-SQL 语句或例程（如存储过程或函数）发送多行数据；

表值参数具有更高的灵活性，在某些情况下，可比临时表或其他传递参数列表的方法提供更好的性能。表值参数具有以下优势：

• 首次从客户端填充数据时，不获取锁。
• 提供简单的编程模型。
• 允许在单个例程中包括复杂的业务逻辑。
• 减少到服务器的往返。
• 可以具有不同基数的表结构。
• 是强类型。
• 使客户端可以指定排序顺序和唯一键。

限制

• 表值参数有下面的限制：

  • SQL Server 不维护表值参数列的统计信息。
  • 表值参数必须作为输入 READONLY 参数传递到 Transact-SQL 例程。不能在例程体中对表值参数执行诸如 UPDATE、DELETE 或 INSERT 这样的 DML 操作。
  • 不能将表值参数用作 SELECT INTO 或 INSERT EXEC 语句的目标。表值参数可以在 SELECT INTO 的 FROM 子句中，也可以在 INSERT EXEC 字符串或存储过程中。

注SqlParameter的SqlDbType定义为SqlDbType.Structured；

TypeName为你所定义表值类型名

value 为 构建的新的内存中的表 

举个例子，创建表值变量

---------------------------------------------------------
 ----创建表值变量Ty_ProductionLocation--------------------
 ---------------------------------------------------------
 CREATE TYPE Ty_ProductionLocation AS TABLE(
 Name NVARCHAR(50) NOT NULL,
 CostRate smallmoney NOT NULL,
 Availability DECIMAL(18,4) NOT NULL
 )
 ---------------------------------------------------------
 ----创建存储过程usp_AddProductLocation-------------------
 ---------------------------------------------------------
 CREATE PROC usp_AddProductLocation
 @dt Ty_ProductionLocation READONLY
 AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @ERRORSUM SMALLINT;
        BEGIN TRAN;
            SET @ERRORSUM =-1;
            INSERT INTO Production.Location(Name,CostRate,Availability,ModifiedDate)
            SELECT tt.*,GETDATE() FROM @dt tt;
            IF(@@ERROR<>0) GOTO ERROR_HANDLE;
        COMMIT TRAN;
        RETURN 0;
ERROR_HANDLE:
        ROLLBACK TRAN;
        RETURN @ERRORSUM;
    END
----------------------------------------------------------
--------调用usp_AddProductLocation存储过程----------------
----------------------------------------------------------
DECLARE @dtt Ty_ProductionLocation;
INSERT INTO @dtt(Name,CostRate,Availability)  SELECT Name,0,0 FROM  Person.StateProvince;
EXEC usp_AddProductLocation @dtt

可以看到，创建表值变量类型，声明变量来引用它，然后给变量填充数据，然后将值传递给存储过程.