通过存储过程实现分页

create procedure [dbo].[CommonPageSelect]
(
@SqlWhere varchar(1000)='', --查询条件
@pagenum int=20,--每页的记录数
@beginline int=1, --第几页,默认第一页
@SqlTable varchar(5000),--要查询的表或视图,也可以一句sql语句
@SqlColumn varchar(4000),--查询的字段
@SqlPK varchar(50),--主键
@SqlOrder varchar(200),--排序
@Count int=0 output
)
as
set nocount on
declare @PageLowerBound int
declare @PageUpperBound int
declare @sqlstr nvarchar(2000)

--获取记录数
IF @beginline=1 --可根据实际要求修改条件
BEGIN
set @sqlstr=N'select @sCount=count(1) FROM '+@SqlTable+' WHERE 1=1'+@SqlWhere
Exec sp_executesql @sqlstr,N'@sCount int outPut',@Count output
END
--print @Count
/**/
set @PageLowerBound=(@beginline-1)*@pagenum
set @PageUpperBound=@PageLowerBound+@pagenum
create table #pageindex(id int identity(1,1) not null,nid int)
set rowcount @PageUpperBound
set @sqlstr=N'insert into #pageindex(nid) select '+@SqlPK+' from '+@SqlTable+' where 1=1 '+@SqlWhere+@SqlOrder

Exec sp_executesql @sqlstr

set @sqlstr='select '+@SqlColumn+' FROM '+ @SqlTable +' inner join #pageindex p on '+@SqlPK+'=p.nid and (p.id>'+STR(@PageLowerBound)+') and (p.id<='+STR(@PageUpperBound)+')' +@SqlOrder

Exec sp_executesql @sqlstr
set nocount off
drop table #pageindex