create procedure sp_vlist @cpbmb varchar(6),@crbh varchar(2) with encryption as
set nocount on
declare @sqlcmd varchar(148)
if object_id('tempdb..##vlist') is not null
drop table ##vlist
set @sqlcmd='select identity(int, 1,1) as id, * into ##vlist from '+@cpbmb+' where crbh='''+@crbh+''' order by cpxh
create index ##vlist_id on ##vlist(id)'
exec(@sqlcmd)
set nocount off
/*以下是我以前自己写的一个存储过程,希望对你有所帮助
其中t_myTable的id是一个int自增型字段,在我的应用中,是根据这个id来分页的
*/
CREATE PROCEDURE sp_SearchResults
@PageSize int,
@CurrentPage int,
@RecordCount int Output,
@PageCount int Output
AS
declare @BeginRow int
declare @EndRow int
declare @BeginID int
declare @EndID int
set @RecordCount = (select count(*) from t_myTable)
set @PageCount = Round((@RecordCount / @PageSize), 0) + 1
--如果@PageSize刚刚能整除@RecordCount,则@PageCount减1
if @RecordCount = ((@PageCount - 1) * @PageSize)
set @PageCount = @PageCount - 1
--如果当前页的数值大于总页数,则把当前页重设为总页数的值
if (@CurrentPage > @PageCount)
set @CurrentPage = @PageCount
--当结果集不为空时,查询返回当前页的结果集
if @RecordCount > 0
begin
--取的当前页的开始位置和结束位置
set nocount on
set @BeginRow = @PageSize * (@CurrentPage - 1) + 1
set rowcount @BeginRow
select @BeginID = id from t_myTable order by id
set @EndRow = @PageSize * @CurrentPage
if @EndRow >= @RecordCount
set @EndRow = @RecordCount
set rowcount @EndRow
select @EndID = id from t_myTable order by id
set rowcount 0
set nocount off
--取位置结束
--根据开始位置和结束位置返回当前页的记录
select *
from t_myTable
where id between @BeginID and @EndID
order by id
end
else
select * from t_myTable
TSimpleDataSet
TClientDataSet
每调用一次GetNextPacket就可以获得PacketRecords指定个数的记录,直到获得全部记录如果PacketRecords为-1就获得全部记录。
如果用MSSQLServer就用select top n from xxx获得前几行数据,或者用hthunter(核桃)的方法,
如果用Mysql就极简单了
select * from table LIMIT 5,10; 返回6-15行的数据。