問題描述

我有一個很長的查詢，它本質(zhì)上是以下內(nèi)容的擴展:

I have a very long query that is essentially an extension of the following:

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 行.為了使這項任務更容易，我希望做一些類似于以下的事情:

The where clause for this will have ~40 rows when complete. In order to make this task easier I was hoping to do something similar to the following:

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(因此隨后第二個查詢不可用).

Unfortunately lease_id isn't a unique field and there can be multiple lease_id's to the same suite_id (so subsequently the second query is unusable).

鑒于此解決方案不起作用，是否有更好的方法來執(zhí)行第一個更新語句?

Is there a better way to do the first update statement given that this solution won't work?

推薦答案

您可以創(chuàng)建表類型并通過它傳遞值，如下所示:

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

如何將表參數(shù)傳遞給存儲過程(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();
            }
        }

這篇關(guān)于需要相互匹配的 where 子句中的多個“in"語句的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！