万能分页存储过程

–分页显示存储过程
if(exists(select * from sys.objects where name=‘pr_ShowPage’))
drop proc pr_ShowPage
go
create proc pr_ShowPage
(
@where nvarchar(500),
@pageIndex int,
@pageSize int,
@totalCount int output,
@pageCount int output
)
as
begin
declare @strcount nvarchar(500)=’’
set @strcount=‘select @totalCount=count() from
(select ROW_NUMBER() over(order by UId) RowNumber, from UserInfo
where 1=1 ‘+@where+’) t’
exec sp_executesql @strcount,N’@totalCount int output’,@totalCount output
set @pageCount=CEILING(@totalCount1.0/@pageSize)
declare @strsql nvarchar(500)=’’
declare @startRow int,@endRow int
set @startRow=(@pageIndex-1)@pageSize+1
set @endRow=@pageIndex*@pageSize
set @strsql=‘select * from
(select ROW_NUMBER() over(order by UId) RowNumber,* from UserInfo where 1=1 ‘+@where+’) t
where t.RowNumber between ‘+CAST(@startRow as nvarchar(5))+’ and ‘+CAST(@endRow as nvarchar(5))+’’
exec(@strsql)
end

declare @count int,@page int
exec pr_ShowPage ‘’,2,5,@count output,@page output
select @count,@page