Sybase paging Stored Procedure

Source: Internet
Author: User
Tags sybase

/* -- 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.