＋设为首页 ＋添加到收藏夹
我现在有一个大型数据库，有500千万条记录，我要对其进行分页，只希望每次取500条出来。请高手帮忙，分页存储过程!谢谢！
------------------------------------------------------------------------------------
-----大量数据的分页
------------------------------------------------------------------------------------
--查询
if exists (select name from sysobjects where name=ResultRows and type=p)
drop procedure ResultRows
go
CREATE PROCEDURE ResultRows
(
@SQLString nVARCHAR(4000),
@PageCount int,
@ColumnID VARCHAR(255),
@ColumnSort VARCHAR(255)
)
AS
DECLARE @Str nVARCHAR(4000)
SET @Str=SELECT TOP 20 * FROM (+@SQLString+) T WHERE T.+@ColumnID+ NOT IN
(SELECT TOP +CAST((20*(@PageCount-1)) AS VARCHAR(20))+ +@ColumnID+ FROM (+@SQLString+) T9 ORDER BY +@ColumnSort+ desc) ORDER BY +@ColumnSort+ desc
--PRINT @Str
EXEC sp_ExecuteSql @Str
GO
在其他存储过程中调用
--单据列表
if exists (select name from sysobjects where name=SaleBillList and type=p)
drop procedure SaleBillList
go
create procedure SaleBillList
@SaleBargainClient varchar(255),
@SaleFinishDepartment varchar(255),
@SaleFinishPort varchar(255),
@SaleFinishShipName varchar(255),
@PageCount varchar(20)
with encryption
as
declare @SqlString varchar(4000)
set @SqlString=select * from SaleBill,SaleFinish,SalePerform,SaleBargain where SaleBillFinishID=SaleFinishID and SaleFinishPerformID=SalePerformID and SalePerformBargainID=SaleBargainID
if(@SaleBargainClient<>)set @SqlString=@SqlString+ and SaleBargainClient like +%+@SaleBargainClient+%
if(@SaleFinishDepartment<>)set @SqlString=@SqlString+ and SaleFinishDepartment=++@SaleFinishDepartment+
if(@SaleFinishPort<>)set @SqlString=@SqlString+ and SaleFinishPort like +%+@SaleFinishPort+%
if(@SaleFinishShipName<>)set @SqlString=@SqlString+ and SaleFinishShipName like +%+@SaleFinishShipName+%
exec ResultRows @SqlString,@PageCount,SaleBillID,SaleBillID
go
create procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int,--第N页
@pagesize int--每页行数
as
set nocount on
declare @P1 int,--P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
