我有一个很长的查询,本质上是以下内容的扩展:
update property.lease_period set scca_uplift = '110', scca_notes_code = '21006' where (suite_id = 'CCBG08' and lease_id = '205059') or (suite_id = 'CCBG14' and lease_id = '152424') or (suite_id = 'CCCF048' and lease_id = '150659')
完成后,其中的where子句将有约40行。为了使此任务更容易,我希望做与以下类似的事情:
update property.lease_period set scca_uplift = '110', scca_notes_code = '21006' where suite_id in('CCBG08', 'CCBG14', 'CCCF048') and lease_id in('205059', '152424', '150659')
不幸的是,lease_id不是唯一字段,同一个suite_id可以有多个lease_id(因此,第二个查询不可用)。
鉴于此解决方案无法正常工作,是否有更好的方法来执行第一条更新语句?
您可以创建表类型并通过它传递值,如下所示:
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
如果您使用多个“ big” where子句,则可以使 过程高速缓存 保持干燥整洁
如何将表参数传递到存储过程(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(); } }