SQL -- Process Multi-condition query and SQL page query on the page

SQL -- Process Multi-condition query and SQL page query on the page

Two methods are used to process multi-condition queries in the past. The first method is to determine the fields to be filled in the condition, and then determine whether the input parameter is null. If it is null, use 1 = 1 to replace this condition. The second method first checks whether the input parameter is null. If not, concatenate the condition into a single character, finally, execute the string using exec. Recently, I am still familiar with it. Actually, it is boring. However, during the process of chatting with his eldest brother, he wrote a case... when... method for me and posted it to you:

Alter procedure [dbo]. [sp_LoadSellerDataByPartner] -- Common parameter @ PartnerCode NVARCHAR (36), -- Partner code @ pageSize INT = 10, -- how many pieces of data are on a page @ pageIndex INT = 1, -- page number ---- multi-condition query parameters (merchant ID, merchant name, arrival time, partner name) @ QureSellerNumber NVARCHAR (36), -- Merchant @ QuerSellerName NVARCHAR (100 ), -- Merchant name @ QuerBuildTime NVARCHAR (36), -- entry time @ QueryUserName NVARCHAR (100) -- Partner name ASDECLARE @ totalCount INT; -- total number of pieces of data -- query the merchant ID, merchant name, merchant address, entry time, partner name, current order quantity, cumulative turnover WITH temp AS (SELECT s. [SellerNumber]/* merchant ID */, s. code,/* merchant code */s. [SellerName]/* merchant name */, s. [Address]/* merchant Address */, LEFT (convert (varchar, s. [BuildTime], 21), 10) AS BuildTime/* entry time */, cpu. [UserName]/* partner name */, (select count (1) FROM CommunityCatering. catering. [Order] o WHERE o. sellerCode = s. code AND o. completeTime between dateadd (MONTH, DATEDIFF (MONTH, 0, GETDATE (), 0) and getdate () AS OrderCount/* query the order quantity from the beginning of the MONTH to the present */, (select sum (TotalCharge) FROM [CommunityCatering]. [Catering]. [SellerIncome] si where si. createTime between dateadd (MONTH, DATEDIFF (MONTH, 0, GETDATE (), 0) and getdate () AND s. code = SI. sellerCode and si. validStatus = 1) AS SumMonthMoney/* Current turnover (pay attention to determine the validity) */, (select sum (TotalCharge) FROM [CommunityCatering]. [Catering]. [SellerIncome] sa where sa. sellerCode = s. code and sa. validStatus = 1) AS SumTotalMoney/* cumulative turnover (pay attention to determine the validity) */, ROW_NUMBER () OVER (order by s. [SellerNumber]) AS RowNum/* fields required for paging */FROM [CommunityCatering]. [Catering]. [Seller] s left join [CommunityCatering]. [Catering]. [CateringPartnerUser] cpu ON cpu. code = s. [PartnerUserCode] WHERE s. validStatus = 1/* approved by sellers */AND cpu. code = @ PartnerCode -- partners are required here (for example, 'ff48648d-4e61-b702-4f12-80ed41d791ae ') SELECT * FROM temp WHERE/* Add query conditions to: merchant ID, merchant name, arrival time, partner name */-- Merchant ID (case when isnull (@ QureSellerNumber, '0') = '0' OR @ QureSellerNumber = ''THEN 1 else cast (CHARINDEX (@ QureSellerNumber, temp. [SellerNumber]) as bit) END) = 1 AND -- Merchant name (case when isnull (@ QuerSellerName, '0 ') = '0' OR @ QuerSellerName = ''then 1 else cast (CHARINDEX (@ QuerSellerName, temp. [SellerName]) as bit) END) = 1 AND -- Enter time (case when isnull (@ QuerBuildTime, '0 ') = '0' OR @ QuerBuildTime = ''' THEN 1 else cast (CHARINDEX (@ QuerBuildTime, temp. [BuildTime]) as bit) END) = 1 AND -- Partner name (case when isnull (@ QueryUserName, '0 ') = '0' OR @ QueryUserName = ''' THEN 1 else cast (CHARINDEX (@ QueryUserName, temp. [UserName]) as bit) END) = 1 AND temp. rowNum BETWEEN (@ pageSize) * (@ pageIndex-1) + 1) and (@ pageIndex) * (@ pageSize) /* Paging Based on rownum */-- total number of output rows: SELECT @ totalCount = (select count (s. [SellerNumber]) FROM [CommunityCatering]. [Catering]. [Seller] s left join [CommunityCatering]. [Catering]. [CateringPartnerUser] cpu ON cpu. code = s. [PartnerUserCode] WHERE cpu. code = @ PartnerCode) -- SELECT @ totalCount for the total number of output rows

I personally feel that this method is also quite clever. If you are interested in it, you can find out the usage of case... and it feels quite fun.