/*--sybase分页操作--*/
--方法1:
--*********************************************
--按起始记录条数与终结记录条数进行划分
--@sqlStr为普通查询语句，语句中不能包括表中已有
--的Identity类型字段，否则存储过程将不能正常工作
--例:语句可以为select * from TestTable
--*********************************************
if(exists(select * from sysobjects where type = 'P' and name = 'sp_SplitPage1'))
drop proc sp_SplitPage1
go
create procedure sp_SplitPage1
(
@sqlStr varchar(8000), --需要进行查询的数据集
@startRecNum varchar(20), --起始记录行
@endRecNum varchar(20) --结束记录行
)
as
begin
declare @randnum varchar(20)
set @randnum = substring(convert(varchar,rand()),3,10)
set @sqlStr = stuff(@sqlStr,charindex(' from ',@sqlStr),6,' into tempdb..mytemp' + @randnum + ' from ')
execute (@sqlStr)
set @sqlStr = 'alter table tempdb..mytemp' + @randnum + ' add keyId numeric(10) IDENTITY primary key'
execute (@sqlStr)
set @sqlStr = 'select * from tempdb..mytemp' + @randnum + ' where keyId >= ' + @startRecNum
+ ' and keyId <= ' + @endRecNum
execute (@sqlStr)
set @sqlStr = 'drop table tempdb..mytemp' + @randnum
execute (@sqlStr)
end
go
--测试用例
declare @curdate datetime
set @curdate = getdate()
use TestDB
exec sp_SplitPage1 'select ID,Name from TestTable','1','9'
select datediff(millisecond,@curdate,getdate())
go
--方法2:
--*********************************************
--给出当前页数与每页记录数进行分页
--@sqlStr为普通查询语句，语句中不能包括表中已有
--的Identity类型字段，否则存储过程将不能正常工作
--例:语句可以为select * from TestTable
--*********************************************
if(exists(select * from sysobjects where type = 'P' and name = 'sp_SplitPage2'))
drop proc sp_SplitPage2
go
create procedure sp_SplitPage2
(
@sqlStr varchar(8000), --需要进行查询的数据集
@curPage int, --当前页数
@pageNum int --每页记录数
)
as
begin
declare @startRecNum varchar(20)
declare @endRecNum varchar(20)
--set @sqlStr = 'select DevId,OrgId from Device'
set @startRecNum = convert(varchar,(@curPage - 1) * @pageNum + 1)
set @endRecNum = convert(varchar,(@curPage - 1) * @pageNum + @pageNum)
declare @randnum varchar(20)
set @randnum = substring(convert(varchar,rand()),3,10)
set @sqlStr = stuff(@sqlStr,charindex(' from ',@sqlStr),6,' into tempdb..mytemp' + @randnum + ' from ')
execute (@sqlStr)
set @sqlStr = 'alter table tempdb..mytemp' + @randnum + ' add keyId numeric(10) IDENTITY primary key'
execute (@sqlStr)
set @sqlStr = 'select * from tempdb..mytemp' + @randnum + ' where keyId >= ' + @startRecNum
+ ' and keyId <= ' + @endRecNum
execute (@sqlStr)
set @sqlStr = 'drop table tempdb..mytemp' + @randnum
execute (@sqlStr)
end
go
--测试用例
declare @curdate datetime
set @curdate = getdate()
use TestDB
exec sp_SplitPage2 'select ID,Name from TestTable',3,5
select datediff(millisecond,@curdate,getdate())
go
