SQL分页存储过程

USE [Renner]
GO
/****** Object:  StoredProcedure [dbo].[Paging]    Script Date: 2021/7/14 18:08:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[Paging]--'1','5','洪飞','',''
(
    @PageIndex int,
    @PageSize int,
    @userName varchar(50)='',
    @id varchar(50)='',
    @PageCount int=0 out
)
as
begin
    --定义一个Nvarchar的恒成立的Sql语句
    declare @Sql Nvarchar(1000) = N'select * from Users where 1 = 1 '
    --判断查询框DName是否有值
    if(len(@userName)>0)
        --有值就把这句sql语句拼接到恒成立的sql语句里
        set @Sql += ' and userName like ''%'+@userName+'%'''
    --判断查询框BName是否有值
    if(len(@id)>0)
        --有值就把这句sql语句拼接到恒成立的sql语句里
        set @Sql += ' and id = '+@id+''
    --把拼接的字符串转成表明别名TT
    set @Sql = '('+@Sql+')TT'
    --总记录数
    declare @SqlCount Nvarchar(1000) = N'select @PageCount = count(*) from'+@Sql
    --系统存储过程拼接字符串
    exec sp_executesql @sqlCount,N'@PageCount int out',@PageCount out
    --分页
    declare @SqlPaging Nvarchar(1000) = 'select top('+convert(varchar(20),@PageSize)+') * from  '+@Sql+'
    where id not in(select top('+convert(varchar(20),((@PageIndex - 1)*@PageSize))+') id from '+@Sql+')'
    --系统存储过程执行字符串
    exec sp_executesql @sqlpaging
    --select @PageCount PageCount,@PageSize PageSize
    select @PageIndex currPage,CEILING(@PageCount%@PageSize) totalPages,@PageCount Total
end
----测试分页结果
--declare @Count int
--exec Paging 1,3,'','',@Count out
--select @Count