SQL Service 分页存储过程

USE [Logistics]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[PROC_GetDriverPagedList]
(
    @Name nvarchar(32),
    @Sex char(2),
    @BirthStart varchar(32),
    @BirthEnd varchar(32),
    @PageIndex int,
    @PageSize int,
    @TotalCount int output
)
as 
declare @StrSql nvarchar(1024)--定义动态SQL语句
declare @StrWhere nvarchar(1024)--定义多条件查询的and 语句
declare @PageCount int--定义总页数

set @StrWhere=''--为多条件查询的and语句赋值为''字符串

if @Name<>'' and @Name is not null
begin
    set @StrWhere=@StrWhere+' and d.Name='''+@Name+''''
end

if @Sex<>'' and @Sex is not null
begin
    set @StrWhere=@StrWhere+' and d.Sex='''+@Sex+''''
end

if @BirthStart<>'' and @BirthStart is not null
begin
    set @StrWhere=@StrWhere+' and d.Birth>='''+@BirthStart+''''
end

if @BirthEnd<>'' and @BirthEnd is not null
begin
    set @StrWhere=@StrWhere+' and d.Birth<'''+@BirthEnd+''''
end

print @StrWhere

--计算当前数据的总记录数
set @StrSql='select @TotalCount=count(*) from Driver as d where IsDelete=0'+@StrWhere

--执行SQL计算出总的记录数
exec sp_executesql @StrSql ,N'@TotalCount int output',@TotalCount output

--边界值判断
if @PageIndex<1
begin
    set @PageIndex=1
end

--计算出来总页数
set @PageCount= CEILING(CONVERT(float, @TotalCount)/@PageSize)

if @PageIndex>@PageCount
begin
    set @PageIndex=@PageCount
end

set @StrSql=N'select * from 
(
select ROW_NUMBER() over(order by DriverID) as RowIndex, d.DriverID, d.Name,d.Sex,d.Phone,d.Birth,tt.TeamName,d.IDCard,d.CheckInTime,t.Number from Driver as d left join Contact as c on d.DriverID=c.FK_DriverID
left join Truck as t on t.TruckID = c.FK_TruckID
left join TruckTeam as tt on tt.TeamID = d.FK_TeamID where d.IsDelete=0 '+@StrWhere+'
)as temp where temp.RowIndex between '+convert(varchar(16), (((@PageIndex-1)*@PageSize)+1))+' and '+convert( varchar(16),(@PageIndex*@PageSize))

exec sp_executesql @StrSql