工作中编写存储过程小记

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

 

 

 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

SQL存储过程

 重点解释部分：

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

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

转载于:https://www.cnblogs.com/ganqiyin/p/3460161.html