大家帮忙看看这个存储过程能否用于百万数据级的翻页？
大家帮忙看看这个存储过程能否用于百万数据级的翻页？
CREATE PROCEDURE zkx_Web_TelesellAdmin_getMsgList
(@tblTable nvarchar(200), --要操作的数据表
@pagesize int, --每页显示页数
@pageindex int, --当前页码
@isFirst bit, --是否是第一页 为 0 显示第一页内容 为 1 显示翻页内容
@doCount bit --操作类型 0 表示获取记录总数 1 表示获取记录信息
)
AS
set nocount on
if(@doCount=0)
exec( 'select count(ID) from '+@tblTable)
else
declare @sql Nvarchar(1000)
if @isFirst=0
set @sql= 'select top '+ltrim(@pagesize)+ ' * from '+@tblTable+ ' order by ID desc '
else
set @sql= 'select top '+ltrim(@pagesize)+ ' * from '+@tblTable+ ' where (ID not in(select top '+ltrim((@pageindex-1)
*@pagesize)+ ' ID from '+@tblTable+ ' order by ID desc)) order by ID desc '
exec(@sql)
set nocount off
GO
[解决办法]
建议使用：@@RowCount
[解决办法]
使用top max的方案会比较好些,但如果你用的是SQL 2005的话可能会有更方便的手段.
[解决办法]
同意楼上
CREATE PROCEDURE P_TopWinCMS_PageList
@tablename varchar(100),
@fields varchar(800),@condition varchar(8000),
@orderfield varchar(100),@ordertype varchar(100),
@pagesize int,@PageNo int
AS
declare @SQL varchar(8000)
if (@ordertype = 'Desc ')
begin
if (@condition = 'None ')
begin
if (@PageNo = 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) + ' '+ @fields + ' From '+ @tablename + '
Order By '+ @orderfield + ' Desc '
end
if (@PageNo > 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) + ' '+ @fields + ' From '+ @tablename + '
Where '+ @orderfield + ' <(Select Min( '+ @orderfield + ') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) + ' '+ @orderfield
+ ' From '+ @tablename + ' Order By '+ @orderfield + ' Desc) Temp) Order By '+ @orderfield + ' Desc '
end
end
else
begin
if (@PageNo = 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) + ' '+ @fields + ' From '+ @tablename + ' '+ @condition + ' Order By '+ @orderfield + ' Desc '
end
if (@PageNo > 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) + ' '+ @fields + ' From '+ @tablename + ' '+ @condition + ' And '+ @orderfield + ' <(Select Min( '+ @orderfield + ') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) + ' '+ @orderfield + ' From '+ @tablename + ' '+ @condition + ' Order By '+ @orderfield + ' Desc) Temp) Order By '+ @orderfield + ' Desc '
end
end
end
else if (@condition = 'None ')
begin
if (@PageNo = 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) + ' '+ @fields + ' From '+ @tablename + ' Order By '+
@orderfield + ' Asc '
end
if (@PageNo > 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) + ' '+ @fields + ' From '+ @tablename + ' Where '+
@orderfield + '> (Select Max( '+ @orderfield + ') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) + ' '+ @orderfield + ' From '+
@tablename + ' Order By '+ @orderfield + ' Asc) Temp) Order By '+ @orderfield + ' Asc '
end
end
else
begin
if (@PageNo = 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) + ' '+ @fields + ' From '+ @tablename + ' '+
@condition + ' Order By '+ @orderfield + ' Asc '
end
if (@PageNo > 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) + ' '+ @fields + ' From '+ @tablename + ' '+
@condition + ' And '+ @orderfield + '> (Select Max( '+ @orderfield + ') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) + ' '+
@orderfield+ ' From '+ @tablename + ' '+ @condition + ' Order By '+ @orderfield + ' Asc) Temp) Order By '+ @orderfield + ' Asc '
end
end
exec (@SQL)
GO
