更新时间:2023-10-23 14:01:58
您可以创建表类型并通过它传递值,如下所示:
You may create table type and pass the values thru it, like that:
CREATE TYPE Suite_Lease AS TABLE
(
suite_id varchar(15) NOT NULL,
lease_id varchar(15) NOT NULL
)
GO
CREATE PROC DoUpdate
@Params Suite_Lease READONLY,
@uplift varchar(15),
@code varchar(15)
AS
update property.lease_period set
scca_uplift = @uplift,
scca_notes_code = @code
from property.lease_period tab
JOIN @params filt
on tab.suite_id=filt.suite_id AND tab.lease_id=filt.lease_id
这将使您的过程缓存保持干燥和清洁,而不是如果您使用多个大" where 子句
This will keep your Procedure cache dry and clean, instead if you using multiple "big" where clauses
如何将表参数传递给存储过程(c#):
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("suite_id", typeof (string)) {AllowDBNull = false, MaxLength = 15});
dt.Columns.Add(new DataColumn("lease_id", typeof (string)) {AllowDBNull = false, MaxLength = 15});
dt.Rows.Add("CCBG08", "205059");
... add more rows for match
using (var c = new SqlConnection("ConnectionString"))
{
c.Open();
using(var sc = c.CreateCommand())
{
sc.CommandText = "DoUpdate";
sc.CommandType = CommandType.StoredProcedure;
sc.Parameters.AddWithValue("@uplift", "110");
sc.Parameters.AddWithValue("@code", "21006");
sc.Parameters.Add(new SqlParameter("@Params", SqlDbType.Structured) { TypeName = null, Value = dt });
sc.ExecuteNonQuery();
}
}