SQL Server基本存储过程分页

if (exists (select * from sys.objects where name = 'Page_UserInfo')) drop proc Page_UserInfo   --判断存储过程是否存在，存在则删除然后重建。
go
create proc Page_UserInfo  --创建存储过程 
    @name nvarchar(255),--用户名
    @pageindex int,--第几页
    @pagesize int--一页多少条
as 
set nocount on;  --不返回计数，提高应用程序性能
begin --开始
  declare @pagebefore int;--创建页数
    declare @pagerear int;--创建页数
  declare @condition nvarchar(2000);  --创建where条件
    set @pagebefore=@pagesize*@pageindex; --起始页
    set @pagerear=@pagebefore+@pagesize;--结束页
    set @condition=' where 1=1 ';
    if(@name<>'')
    set @condition=@condition+' and name like ''%'+@name+'%''';
  --创建一个虚拟表插入UserInfo表数据
  --获取分页数据
  --获取总数
    exec('
    declare @table table(
    iid int identity,
    Id int,
    Name nvarchar(20),
    Sex int,
    Age int,
    Birthday datetime
    )
    insert @table
    select * from UserInfo '+@condition+' order by Id desc  
    select * from @table where iid>'+@pagebefore+' and iid<='+@pagerear+'
    select count(*) as rows from @table;');
end;--结束

调用方式
EXEC Page_UserInfo  '' ,1,10      (赋值:用户名,页数,一页多少条)

USE [newHRP]
GO
/****** Object:  StoredProcedure [dbo].[Pro_TradeRecord]    Script Date: 11/10/2020 11:49:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure Pro_TradeRecord
@StartTime datetime,@EndTime datetime
as
if @StartTime=''
begin
set @StartTime=DateAdd(MM,DATEDIFF(MM,0,getdate()),0)
end
if @EndTime=''
begin
set @EndTime=DateAdd(MI,-1,DateAdd(MM,DATEDIFF(MM,0,getdate())+1,0))
end
else
begin
set @EndTime=CONVERT(char(10),@EndTime,23)+' 23:59:00'
end
select CS_TRTradeDate as 日期,ZaoCan as 早餐,ZaoCanRenShu as 早餐人数,ZhongCan as 中餐,ZhongCanRenShu as 中餐人数,WangCan as 晚餐,WangCanRenShu as 晚餐人数,ZaoCanRenShu+ZhongCanRenShu+WangCanRenShu as 用餐人数统计,ZaoCan+ZhongCan+WangCan as 用餐金额统计,ChaoShiJinE as 超市金额,ChaoShiRenShu 超市人数,TanFenJinE as 汤粉面档金额,TanFenRenShu as 汤粉面档人数,ZaoCan+ZhongCan+WangCan+ChaoShiJinE+TanFenJinE as 合计

from
(
select
convert(char(10),CS_TRTradeTime,23) as CS_TRTradeDate,
sum(case when a.CS_TDName = '早餐' and c.Sys_RGName='饭堂' then CS_TRTradeMoney else 0 end) as ZaoCan,
sum(case when a.CS_TDName = '早餐'and c.Sys_RGName='饭堂' then 1 else 0 end) as ZaoCanRenShu,
sum(case when a.CS_TDName = '中餐' and c.Sys_RGName='饭堂' then CS_TRTradeMoney else 0 end) as ZhongCan,
sum(case when a.CS_TDName = '中餐' and c.Sys_RGName='饭堂' then 1 else 0 end) as ZhongCanRenShu,
sum(case when a.CS_TDName = '晚餐' and c.Sys_RGName='饭堂' then CS_TRTradeMoney else 0 end) as WangCan,
sum(case when a.CS_TDName = '晚餐' and c.Sys_RGName='饭堂' then 1 else 0 end) as WangCanRenShu,
sum(case c.Sys_RGName when '超市' then a.CS_TRTradeMoney else 0 end) as ChaoShiJinE,
sum(case c.Sys_RGName when '超市' then 1 else 0 end) as ChaoShiRenShu,
sum(case c.Sys_RGName when '汤粉面档' then a.CS_TRTradeMoney else 0 end) as TanFenJinE,
sum(case c.Sys_RGName when '汤粉面档' then 1 else 0 end) as TanFenRenShu
from CS_TradeRecord a left join   sys_equipment b on a.Sys_EquipmentID=b.Sys_EquipmentID 
left join Sys_Region c on b.Sys_RegionID=c.Sys_RegionID 
where CS_TRTradeTime between @StartTime and @EndTime
GROUP BY convert(char(10),CS_TRTradeTime,23)
)T