代码语言：javascript复制
ALTER PROCEDURE [dbo].[procPageChangeForSql2005]
(
@fromSql varchar(4000),
@querySql varchar(4000),
@whereSql varchar(max),
@orderBySql varchar(200),
@keyField varchar(100),
@PageSize int, --每页多少条记录
@PageIndex int, --指定当前为第几页
@TotalRecord int output --返回总记录数
)
AS
BEGIN
declare @sql nvarchar(max)
declare @totalCountSql nvarchar(MAX)
declare @StartRecord int
declare @EndRecord int declare @nPageCount int --处理开始点和结束点
IF (@whereSql! = '' AND @whereSql IS NOT NULL)
set @whereSql=' where ' @whereSql
else set @whereSql=''
--计算总条数
set @totalCountSql= N'select @TotalRecord = count(*) from ' @fromSql @whereSql --总记录数语句
exec sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数
if(@PageSize>-1)
begin set @nPageCount =CONVERT(int, ceiling(@TotalRecord*1.0/@PageSize))
if(@nPageCount=0 and @PageIndex != 0)
set @PageIndex = 0
else if (@PageIndex > (@nPageCount - 1))
set @PageIndex = 0
set @StartRecord = (@PageIndex)*@PageSize 1
set @EndRecord = @StartRecord @PageSize - 1 --组织Sql语句 set @sql ='(select row_number() over (order by ' @orderBySql ') as rowId,' @querySql ' from ' @fromSql @whereSql;--查询语句 set @sql ='select * from ' @sql ') as t where rowId between ' ltrim(str(@StartRecord)) ' and ' ltrim(str(@EndRecord));
end
else
begin IF (@orderBySql! = '' AND @orderBySql IS NOT NULL) set @orderBySql=' order by ' @orderBySql else set @orderBySql='' set @sql='select ' @querySql ' from ' @fromSql @whereSql @orderBySql
end
Exec(@sql)
END
