sql--processing a page multi-criteria Query

In the previous process of multi-conditional query, mainly two methods, the first is to determine the condition inside the field to fill in the conditions, and then determine whether the incoming parameter is empty, if it is empty, replace the condition with 1=1, the second method, first determine whether the parameter is empty, if not empty, the condition is stitched into a character inside, Finally, I use Exec to execute the string; I'm still familiar with this thing lately, and, well, it's really boring. But in the process of doing with the next big Brother gossip, he wrote to me a kind of case...when...end. The way, put it out for everyone to look at:

ALTER PROCEDURE [dbo]. [Sp_loadsellerdatabypartner]--General parameters @PartnerCode NVARCHAR,--Partner Code @pageSize int=10,--How many data on a page @pageIndex int=1        ,--page----Multi-Criteria query parameters (merchant number, merchant name, time of entry, partner name) @QureSellerNumber NVARCHAR (36),--Merchant @quersellername NVARCHAR (100),--Business name @QuerBuildTime NVARCHAR,--occupancy time @queryusername NVARCHAR (100)--partner name Asdeclare @totalCount INT; --How many data--check out the merchant number, business name, business address, time of entry, partner name, number of orders in this issue, cumulative turnover with temp as (SELECT s.[sellernumber]/* merchant number */,s.code,/* merchant code* /s.[sellername]/* Merchant name */,s.[address]/* Merchant address */,left (convert varchar,s.[buildtime],21) as buildtime/* occupancy 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 from the beginning of the month to the present order volume */, (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/* the current turnover (pay attention to validity) */, (SELECT SUM (Totalcharge) from [communitycatering].[ Catering]. [Sellerincome] SA WHERE SA. Sellercode=s.code and SA. Validstatus=1) as sumtotalmoney/* cumulative turnover (note validity) */,row_number () over (ORDER by S.[sellernumber]) as rownum/* paging requires field */ from [communitycatering]. [Catering]. [Seller] s left JOIN [communitycatering]. [Catering]. [Cateringpartneruser] CPU on CPU. Code=s.[partnerusercode] where s.validstatus=1/* audits through merchants */And [email protected] here need partners (for example: ' Ff48648d-4e61-b702-4f12-80ed41d791ae ')) SELECT * FROM temp where/* add query criteria below: Merchant number, merchant name, time of entry, partner's name */-Merchant number NULL (@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--occupancy 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 CA ST (CHARINDEX (@QueryUserName, temp.[ UserName]) as BIT) END) =1 and temp. RowNum Between (((@pageSize) * (@pageIndex-1)) +1) and (@pageIndex) * (@pageSize)/* Paging */--output by RowNum total number of lines: Paging in the program requires this thing select @totalCount = (select COUNT (S.[sellernumber]) from [communitycatering].[ Catering]. [Seller] s left JOIN [communitycatering]. [Catering]. [Cateringpartneruser] CPU on CPU.  Code=s.[partnerusercode] WHERE [email protected])--output calculated total number of rows select @totalCount

Personal feeling, this method is also quite ingenious, interested in child shoes can be found case. The use of this thing, feel very fun.

sql--processing a page multi-criteria Query