工作中编写存储过程小记
实际场景:根据条件去搜索所有的匹配数据,“全部”选项代表是所有符合的,该选项对应的值没有保存在数据库中
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 AS 22 BEGIN 23 --总页数 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.ReceiveType 29 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 = 0 40 BEGIN 41 SELECT c.pkid,c.ChestName,c.ChestType,c.ReceiveType,c.ChestStatus,c.beginDate,c.endDate,c.CreateDate,r.TypeName 42 FROM YHGameCenter.dbo.ChestTask c JOIN ReceiveType r ON r.pkid=c.ReceiveType 43 WHERE 1=2 44 END 45 46 --总页数 47 SET @PCount=CEILING(@out_RecordCount/CONVERT(FLOAT,@in_PageSize)) 48 IF(@in_CurrentPage<1) 49 SET @in_CurrentPage=1 50 IF(@in_CurrentPage>@PCount) 51 SET @in_CurrentPage=@PCount 52 53 --获取符合条件的数据集 54 SELECT t.pkid,t.ChestName,t.ChestType,t.ReceiveType,t.ChestStatus,t.beginDate,t.endDate,t.CreateDate,t.TypeName 55 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.TypeName 57 FROM dbo.ChestTask c JOIN ReceiveType r ON r.pkid=c.ReceiveType 58 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 ) t 67 WHERE (RowNum BETWEEN (@in_CurrentPage -1) * @in_PageSize + 1 AND @in_CurrentPage * @in_PageSize) 68 ORDER BY t.CreateDate DESC 69 END 70 GO
重点解释部分:
(@in_ChestType=0 OR c.ChestType=@in_ChestType):0是表示全部,该条件就是说选择了全部,就匹配第一个条件,选择了其他匹配c.ChestType=@in_ChestType这个条件
DATEDIFF(day,@in_BeginDate,c.beginDate)=0 :表示是同一天
漫漫人生,唯有激流勇进,不畏艰险,奋力拼搏,方能中流击水,抵达光明的彼岸