通用分页存储过程

--判断存储过程是否存在
if Exists(select name from sysobjects where NAME = 'proc_getPage' and type='P')
begin
	drop procedure proc_getPage
end
go

create proc proc_getPage
@tableName varchar(20),--表名
@tableFields varchar(20),--排序字段
@sortOrder varchar(20),--排序方式
@sqlWhere varchar(200),--查询条件
@currentPage int, --当前页
@pageSize int,--每页条数
@totalPage int out--总页数
as
begin
declare @sql nvarchar(500)
declare @totalRecord int

set @sql='select @totalRecord=count(1) from '+@tableName +' where 1=1 '+@sqlWhere
exec sp_executesql @sql, N'@totalRecord int out', @totalRecord out--计算总记录数

set @totalPage=CEILING((@totalRecord+ 0.0)/@pageSize)--得到总页数

set @sql='select * from (
select *,ROW_NUMBER() over(order by '+@tableFields+' '+@sortOrder+') rownumber from '+@tableName+' where 1=1 '+@sqlWhere
+' ) temp where rownumber between '+cast(((@currentPage-1)*@pageSize+1) as varchar(20))+' and '+cast((@currentPage*@pageSize)as varchar(20))
exec(@sql)
end
go

--执行存储过程
--declare @totalPage int
--exec proc_getPage 'userinfo' ,'Id','desc','and Id>0',2,4,@totalPage output
--print @totalPage