代码语言：javascript复制
ALTER PROCEDURE [dbo].[procPageChangeForSql2000]
(
@fromSql varchar(3000),
@querySql varchar(2000),
@whereSql varchar(1300),
@orderBySql varchar(200),
@keyField varchar(100),
@PageSize int, --每页多少条记录
@PageIndex int, --指定当前为第几页
@TotalRecord int output --返回总记录数
)
AS
BEGIN
declare @sql varchar(8000)
declare @totalCountSql nvarchar(4000)
declare @StartRecord int
declare @pageKeyField varchar(100)
declare @whereSql2 varchar(1300) declare @nPageCount int --处理开始点和结束点
IF (@whereSql! = '' AND @whereSql IS NOT NULL)
set @whereSql=' where ' @whereSql
else set @whereSql='' IF (@orderBySql! = '' AND @orderBySql IS NOT NULL)
set @orderBySql=' order by ' @orderBySql
else set @orderBySql='' --获取关键字段 set @pageKeyField= substring(@keyField,CHARINDEX(N'.',@keyField) 1,LEN(@keyField)-CHARINDEX(N'.',@keyField))
--计算总条数
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 --组织Sql语句 IF (@whereSql! = '' AND @whereSql IS NOT NULL) set @whereSql2=@whereSql ' and p_l.' @pageKeyField ' is null ' else set @whereSql2=' where p_l.' @pageKeyField ' is null ' set @sql='select top ' str(@PageSize) ' ' @querySql ' from ' @fromSql ' left join (select top ' str(@StartRecord) ' ' @keyField ' from ' @fromSql @whereSql @orderBySql ') p_l on p_l.' @pageKeyField '=' @keyField @whereSql2 @orderBySql
end
else
begin set @sql='select ' @querySql ' from ' @fromSql @whereSql @orderBySql
end
Exec(@sql)
END
