记录一个古老的Sql分页过程

/*
    根据单位ID获取排班信息
    For：WXX
    TIme：2017-11-22 
*/
ALTER proc [dbo].[proc_ScheduleInfo]
@companyID nvarchar(20)='',    --单位ID
@classLineId nvarchar(20)='',    --线路ID
@vehicleId nvarchar(20)='',        --车辆ID
@goTime nvarchar(30) ='',        --发车日期
@pageIndex int=1,                --当前页
@pageSize int=1000,                --页容量
@numsCount int=0 output,        --总条数
@pageCount int=0 output            --总页数


As


--declare @dateNow nvarchar(20)        --当前日期
--DECLARE    @sql varchar(max)           --查询sql
--DECLARE    @sqlCount varchar(max)      --查询countsql
--DECLARE    @top int
declare @countSql nvarchar(max)            --总条数SQL
declare @infoSql nvarchar(max)        --数据SQL
declare @pageSql nvarchar(max)        --分页SQL
declare @whereSql nvarchar(max)        --条件SQL
declare @execSql nvarchar(max)        --总条数执行SQL
declare @couns nvarchar(max)


set @whereSql = ' 1=1 '


if(@companyID is not null and @companyID <>'')
    set @whereSql = @whereSql+' and companyID= '''+@companyID+''' '
if(@classLineId is not null and @classLineId <>'')
    set @whereSql = @whereSql+' and classLineId = '+@classLineId+' '
if(@vehicleId is not null and @vehicleId <>'')
    set @whereSql = @whereSql+' and vehicleId = '+@vehicleId+' '
if(@goTime is not null and @goTime <>'')
    set @whereSql = @whereSql+' and date like '''+@goTime+'%'' '


 --查询总条数sql
 set @countSql = 'select @count=COUNT(1) from (


     select s.*, d.remark as dr  from
    (
        select * from Schedule where  '+@whereSql+'
    ) as s
    join Company as d on s.companyID = d.cNo
) as b '


--查询数据sql
set @infoSql = 'SELECT Top ('+CONVERT(nvarchar(10),@pageSize)+') * from (
            select row_number()over(order by date)rownumber,* from 
    (
     select s.*, d.remark as dr  from
    (
        select * from Schedule where  '+@whereSql+'
    ) as s
    join Company as d on s.companyID = d.cNo
    ) as b '


set @pageSql = ' ) as T
                Where rownumber >= ('+CONVERT(nvarchar(10),@pageIndex)+'-1) * ('+CONVERT(nvarchar(10),@pageSize)+')+1 Order By rownumber'






--总页数


set @execSql = @countSql
--select(@execSql) for xml path('')
exec sp_executesql @execSql, N'@count int out', @couns out 
set @numsCount = @couns


--分页查询
--select(@infoSql+@whereSql+@pageSql) for xml path('')
exec(@infoSql+@pageSql)




--分页算法
SET @pageCount = @numsCount % @pageSize;   
if (@pageCount =0)  
begin   
    set @pageCount = @numsCount / @pageSize ;  
end   
else if(@numsCount<@pageSize)  
begin   
    set @pageCount=1;  
end   
else   
begin   
 set @pageCount = @numsCount / @pageSize + 1;   
end   
if (@pageIndex>@PageCount)   
begin   
    set @pageIndex = @pageCount;   
end

好记性不如云存储。