Sybase paging Stored Procedure

/* -- Sybase paging operation --*/
-- Method 1:
--*************************************** ******
-- Based on the number of start and end records
-- @ Sqlstr is a common query statement, which cannot contain existing
-- Identity field, otherwise the stored procedure will not work properly
-- Example: the statement can be 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), -- dataset to be queried
@ Startrecnum varchar (20), -- start record line
@ Endrecnum varchar (20) -- end record row
)
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
-- Test Case
Declare @ curdate datetime
Set @ curdate = getdate ()
Use testdb
Exec sp_splitpage1 'select ID, name from testtable', '1', '9'
Select datediff (millisecond, @ curdate, getdate ())
Go

-- Method 2:
--*************************************** ******
-- Returns the current page number and the number of records per page.
-- @ Sqlstr is a common query statement, which cannot contain existing
-- Identity field, otherwise the stored procedure will not work properly
-- Example: the statement can be 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), -- dataset to be queried
@ Curpage int, -- current page number
@ Pagenum int -- number of records per page
)
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
-- Test Case
Declare @ curdate datetime
Set @ curdate = getdate ()
Use testdb
Exec sp_splitpage2 'select ID, name from testtable', 3, 5
Select datediff (millisecond, @ curdate, getdate ())
Go