Sybase paging Stored Procedure

Read by row

Create procedure getdatabyline (-- create a paging read process @ sqlstr varchar (8000), -- SQL statement @ firstrec int, -- page start line @ lastrec int -- page end line) asdeclare @ DT varchar (10) -- generate the random number begin select @ dt = substring (convert (varchar, Rand (), 3, 10) of the temporary table) -- Random Number of a random type -- puts the search result into a temporary table, and the table name is randomly generated, insert 'into '+ random temporary table name select @ sqlstr = stuff (@ sqlstr, charindex ('from', upper (@ sqlstr), 6, 'Into tempdb .. lining '+ @ DT + 'from') execute (@ sqlstr) -- add the idcode select @ sqlstr = 'alter table tempdb .. lining '+ @ DT + 'add tempdb_id numeric (10) Identity primary key' execute (@ sqlstr) -- calculate the number of records in a temporary table -- select @ sqlstr = 'select count (*) from tempdb .. lining '+ @ DT -- execute (@ sqlstr) -- select @ sqlstr = 'select * From tempdb .. lining '+ @ DT + 'where tempdb_id>' + convert (varchar, @ firstrec) + 'and tempdb_id <=' + convert (varchar, @ lastrec) execute (@ sqlstr) -- delete temporary table select @ sqlstr = 'drop table tempdb .. lining '+ @ DT execute (@ sqlstr) end/* Some Comments: 1. @ sqlstr varchar (8000), depends on your page size2. this is a generic paging sp, if you just want to use it for specific table, you 'd better change the 'tempdb .. lining 'to # paging, the performance will be better */

Read by PAGE

Create procedure getdatabypage (-- create a paging read process @ sqlstr varchar (8000), -- SQL statement @ pagesize int, -- number of records per page @ currentpage int -- current page number) asdeclare @ firstrec int, @ lastrec int, @ DT varchar (10) -- start row of the page, end row of the page, generate the random number begin select @ firstrec = (@ currentpage-1) of the temporary table) * @ pagesize -- Calculation page start line select @ lastrec = (@ currentpage * @ pagesize + 1) -- Calculation page end line select @ dt = substring (convert (varchar, rand (),) -- a random number of random types -- add the search results to a temporary table, and the table name is randomly generated, insert 'into '+ random temporary table name select @ sqlstr = stuff (@ sqlstr, charindex ('from', upper (@ sqlstr), 6, 'Into tempdb .. paging '+ @ DT + 'from') execute (@ sqlstr) -- add the idcode select @ sqlstr = 'alter table tempdb .. paging '+ @ DT + 'add tempdb_id numeric (10) Identity primary key' execute (@ sqlstr) -- calculate the number of records in a temporary table -- select @ sqlstr = 'select count (*) from tempdb .. paging '+ @ DT -- execute (@ sqlstr) -- select @ sqlstr = 'select * From tempdb .. paging '+ @ DT + 'where tempdb_id>' + convert (varchar, @ firstrec) + 'and tempdb_id <' + convert (varchar, @ lastrec) execute (@ sqlstr) -- delete temporary table select @ sqlstr = 'drop table tempdb .. paging '+ @ DT execute (@ sqlstr) end/* Some Comments: 1. @ sqlstr varchar (8000), depends on your page size2. this is a generic paging sp, if you just want to use it for specific table, you 'd better change the 'tempdb .. paging 'to # paging, the performance will be better */

Article transferred from: http://hi.baidu.com/liliangwen/blog/item/bdd0c2117c65eb10b8127b3b.html