Database paging Stored Procedure

Sort out the paging stored procedures for each database used in the project.

SQL Server2000 paging stored procedure:
Create procedure p_page_data
(
@ Currentpageindex Int = 1,
@ Pagesize Int = 10,
@ Countrecord int output
)
As
Declare @ currentpagesize int

Declare @ sqlstr nvarchar (1000)

Set @ currentpagesize = @ currentpageindex * @ pagesize;
Begin

Set @ sqlstr = 'select top '+ convert (varchar (50), @ pagesize) +' * from (select top '+ convert (varchar (50), @ currentpagesize) + '* from computer order by id asc) as tablea order by id desc'

Set @ countrecord = (select count (ID) from computer)

Exec (@ sqlstr)
End

Declare @ countrecord int
Exec p_page_data 1, 10, @ countrecord output

SQL server2005 paging stored procedure:

Create procedure p_page_data
(
@ Currentpageindex Int = 1,
@ Pagesize Int = 10,
@ Countrecord int output
)
As
Declare @ currentpagesize int

Declare @ sqlstr nvarchar (1000)

Set @ currentpagesize = @ currentpageindex * @ pagesize;
Begin

Set @ sqlstr = 'select * from (select row_number () over (order by id desc) as rownum, * from computer) A Where rownum> = '+ convert (varchar (50), @ currentpagesize-@ pagesize + 1) +' and rownum <= '+ convert (varchar (50), @ currentpagesize)

Set @ countrecord = (select count (ID) from computer)
Exec (@ sqlstr)
End

Declare @ countrecord int
Exec p_page_data 2,10, @ countrecord output

Oracle10g paging stored procedure:
1.
Create or replace procedure p_pagersum (p_pagesize number, -- number of records per page
P_pageno number, -- current page number, starting from 1
P_sqlselect varchar2, -- query statement, including sorting part
P_outrecordcount out number, -- total number of returned records
Cur_out out GM. Pager. refcursortype)
As
V_ SQL varchar2 (3000 );
V_count number;
V_heirownum number;
V_lowrownum number;
Begin
---- Retrieve the total number of records
V_ SQL: = 'select count (*) from ('| p_sqlselect | ')';
Execute immediate v_ SQL into v_count;
P_outrecordcount: = v_count;
---- Query by PAGE
V_heirownum: = p_pageno * p_pagesize;
V_lowrownum: = v_heirownum-p_pagesize + 1;

V_ SQL: = 'select *
From (
Select a. *, rownum Rn
From ('| p_sqlselect |')
Where rownum <= '| to_char (v_heirownum) |'
) B
Where rn> = '| to_char (v_lowrownum );
-- Pay attention to the use of the rownum alias. For the first time, use rownum directly. For the second time, use the alias RN.

Open cur_out for v_ SQL;

End p_pagersum;

2.

Create or replace procedure p_pagersumtest (p_pagesize number, -- number of records per page
P_pageno number, -- current page number, starting from 1
P_sqlselectfirst varchar2, -- query statement, including sorting part
P_sqlselectend varchar2,
P_outrecordcount out number, -- total number of returned records
Cur_out out GM. Pager. refcursortype)
As
V_ SQL varchar2 (4000 );
V_count number;
V_heirownum number;
V_lowrownum number;
Begin
---- Retrieve the total number of records
V_ SQL: = 'select count (*) from ('| p_sqlselectfirst | p_sqlselectend | ')';
Execute immediate v_ SQL into v_count;
P_outrecordcount: = v_count;
---- Query by PAGE
V_heirownum: = p_pageno * p_pagesize;
V_lowrownum: = v_heirownum-p_pagesize + 1;

V_ SQL: = 'select *
From (
Select a. *, rownum Rn
From ('| p_sqlselectfirst | p_sqlselectend |')
Where rownum <= '| to_char (v_heirownum) |'
) B
Where rn> = '| to_char (v_lowrownum );
-- Pay attention to the use of the rownum alias. For the first time, use rownum directly. For the second time, use the alias RN.

Open cur_out for v_ SQL;

End p_pagersumtest;

The difference between the two paging stored procedures of Oracle10g is that the SQL statement of the request is divided into half, to prevent the transmission of ultra-long SQL statements (when the length is more than 4000 characters, oracle Reports an error when the test process exceeds three thousand characters. This problem can be solved for a long time in the project, and can only be considered to be able to segment the SQL statement.