create proc mypage
@currentIndex as integer, --当前记录位置
@pageCount as integer --每页显示的记录数量
as
declare @sqltext varchar(500)
set @sqltext='select top '+@pageCount+' * from youTable
where indexField >='+@currentIndex
execute (@sqltext)
-----------------------------------------------------------
你的表中必须有一个索引字段,或唯一能排序的字段indexField
如果你的记录集是procedure 生成,你的用临时表
希望对你有所帮助,
@currentIndex as integer, --当前记录位置
@pageCount as integer --每页显示的记录数量
as
declare @sqltext varchar(500)
set @sqltext='select top '+@pageCount+' * from youTable
where indexField >='+@currentIndex
execute (@sqltext)
-----------------------------------------------------------
你的表中必须有一个索引字段,或唯一能排序的字段indexField
如果你的记录集是procedure 生成,你的用临时表
希望对你有所帮助,
select * from #temp where ID_Num between 10 and 20
set @SQLStr='SELECT Top '+cast(@每页大小 as varchar)+' * FROM 表 WHERE 主键列 NOT IN (SELECT TOP '+cast(@每页大小*@第几页 as varchar)+' 主键列 from 表 )'
exec(@SQLStr)
@currentIndex integer, --当前记录位置
@pageCount integer, --每页显示的记录数量
as
declare @sqltext varchar(500)
--生成结果集的临时表;
if object_id('tempdb.dbo.#PageTemptab') is not null
begin
create #PageTemptab (你的临时表的表结构,表结构要与proc的结果结构相同)
insert into #PageTemptab exec youProc
end set @sqltext='select top '+@pageCount+' * from #PageTemptab
where indexField >='+@currentIndex
execute (@sqltext)
if object_id('tempdb.dbo.#PageTemptab') is not null 应改为
---->if object_id('tempdb.dbo.#PageTemptab') is null
@SelectStr nvarchar(1000),
@ColumnStr nvarchar (1000),
@OrderStr nvarchar (1000),
@CurrentPage int,
@PageCount int
as
declare @TimeName nvarchar(25)
declare @TableStr nvarchar(1000)select @TimeName = convert(nvarchar(23), getdate(), 121)
set @TimeName = REPLACE(@TimeName, '.', '')
set @TimeName = REPLACE(@TimeName, ':', '')
set @TimeName = REPLACE(@TimeName, '-', '')
set @TimeName = REPLACE(@TimeName, ' ', '')select @TableStr='create table ##Tab' + @TimeName + '(wb int identity,'
exec(@TableStr+@ColumnStr+')')
exec('insert into ##Tab' + @TimeName + ' ' + @SelectStr + ' order by ' + @OrderStr)
exec('select * from ##Tab' + @TimeName + ' where wb between ((' + @CurrentPage + '-1)*' + @PageCount + '+1) and ' + @CurrentPage + '*' + @PageCount + ' order by wb')
exec('drop table ##Tab' + @TimeName)
GO参数1:select语句。2:字段列表。3:排序字段。4:当前页。5每页数目
@lastProductID int,
@pageSize int
AS
SET ROWCOUNT @pageSize
SELECT *
FROM Products
WHERE [standard search criteria]
AND ProductID > @lastProductID
ORDER BY [Criteria that leaves ProductID monotonically increasing]
GO
select IDENTITY(int,1,1) as iid,* into #temptable from yourtable
select top M-N * from #temptable where iid>=NOR:select top M-N * from yourTable where id not in(select top N-1 id from table)
ID为表具有唯一值的任何字段