实际场景：根据条件去搜索所有的匹配数据，“全部”选项代表是所有符合的，该选项对应的值没有保存在数据库中

 

 

1 -- 删除存储过程 2 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SpGs_ChestTask_SearchList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) 3  4 drop procedure [dbo].SpGs_ChestTask_SearchList 5 GO 6  7 --创建存储过程 8 create procedure SpGs_ChestTask_SearchList 9 (10    @out_RecordCount        INT OUTPUT        --数量总条数11     , @in_CurrentPage        INT    = 1            --第几页  12     , @in_PageSize            INT    = 10        --每页数量13     , @in_ChestType         INT = 0         --0:全部，1登录宝箱，2会员宝箱14     , @in_ChestName         VARCHAR(50)=''  --宝箱名称15     , @in_ReceiveType       INT = 0         --0:全部，1按倒计时领取，2按固定时间段领取,3签到领取,4按日期段领取,5月宝箱,6节日发奖16     , @in_SendStatus        INT = 0         --2：全部，0:未发布，1发布17     , @in_BeginDate         datetime=''     --开始时间18     , @in_EndDate           datetime=''     --结束时间19     , @in_CreateDate        datetime=''     --创建时间20 )21 AS22   BEGIN23     --总页数24     DECLARE @PCount        INT    25     --数据总条数26     --开始时间等于开始时间，结束时间等于结束时间27     SELECT @out_RecordCount=COUNT(c.pkid) 28         FROM dbo.ChestTask c JOIN ReceiveType r ON r.pkid=c.ReceiveType29         WHERE (@in_ChestType=0 OR c.ChestType=@in_ChestType)30         AND c.ChestName LIKE '%'+@in_ChestName+'%' 31         AND (@in_ReceiveType=0 OR c.ReceiveType=@in_ReceiveType)32         AND (@in_SendStatus=2 OR c.ChestStatus=@in_SendStatus)33         AND (@in_BeginDate='' OR DATEDIFF(day,@in_BeginDate,c.beginDate)=0)34         AND (@in_EndDate='' OR DATEDIFF(day,@in_EndDate,c.endDate)=0)35         AND (@in_CreateDate='' OR DATEDIFF(day,@in_CreateDate,c.CreateDate)=0)36         37       38     --当没有记录数据时，返回结构39     IF @out_RecordCount = 040     BEGIN41         SELECT c.pkid,c.ChestName,c.ChestType,c.ReceiveType,c.ChestStatus,c.beginDate,c.endDate,c.CreateDate,r.TypeName42         FROM YHGameCenter.dbo.ChestTask c JOIN ReceiveType r ON r.pkid=c.ReceiveType43         WHERE 1=244     END    45     46     --总页数47     SET @PCount=CEILING(@out_RecordCount/CONVERT(FLOAT,@in_PageSize))48     IF(@in_CurrentPage<1)49         SET @in_CurrentPage=150     IF(@in_CurrentPage>@PCount)51         SET @in_CurrentPage=@PCount52         53     --获取符合条件的数据集54     SELECT t.pkid,t.ChestName,t.ChestType,t.ReceiveType,t.ChestStatus,t.beginDate,t.endDate,t.CreateDate,t.TypeName55         FROM (56             SELECT ROW_NUMBER() OVER(ORDER BY c.pkid  DESC) as RowNum, c.pkid,c.ChestName,c.ChestType,c.ReceiveType,c.ChestStatus,c.beginDate,c.endDate,c.CreateDate,r.TypeName57             FROM dbo.ChestTask c JOIN ReceiveType r ON r.pkid=c.ReceiveType58                 WHERE (@in_ChestType=0 OR c.ChestType=@in_ChestType)59                   AND c.ChestName LIKE '%'+@in_ChestName+'%' 60                   AND (@in_ReceiveType=0 OR c.ReceiveType=@in_ReceiveType)61                   AND (@in_SendStatus=2 OR c.ChestStatus=@in_SendStatus)62                   --开始时间，结束时间相等63                   AND (@in_BeginDate='' OR DATEDIFF(day,@in_BeginDate,c.beginDate)=0)64                   AND (@in_EndDate='' OR DATEDIFF(day,@in_EndDate,c.endDate)=0)65                   AND (@in_CreateDate='' OR DATEDIFF(day,@in_CreateDate,c.CreateDate)=0)66               ) t67         WHERE (RowNum BETWEEN (@in_CurrentPage -1) * @in_PageSize + 1 AND @in_CurrentPage * @in_PageSize)68         ORDER BY t.CreateDate DESC69   END70 GO

 重点解释部分：

    (@in_ChestType=0 OR c.ChestType=@in_ChestType)：0是表示全部，该条件就是说选择了全部，就匹配第一个条件，选择了其他匹配c.ChestType=@in_ChestType这个条件

    DATEDIFF(day,@in_BeginDate,c.beginDate)=0 ：表示是同一天