A common stored procedure paging program in DB2

CREATE PROCEDURE SALES. Db2pagination (in Itbname VARCHAR),--table name
In Ishowfield VARCHAR (1000),--display field
In Ijoin VARCHAR (1000),--join conditions (for example: inline, outreach)
In Iwhere VARCHAR,--query Criteria (Note: Do not add WHERE)
In Iorder VARCHAR,--sort criteria (note: Do not add order by)
In ipagesize INTEGER,--page size if 0 defaults to return the first 1 million data can be considered to return all data
INOUT Iocurrentpageix INTEGER,--input and output: current page
Out Opagestartrow INTEGER,--output: Current start line
Out Opageendrow INTEGER,--output: Current end Line
Out ototalrows INTEGER,--output: Number of current total records
Out ohaspreviouspage INTEGER,--output: Whether there is a previous page
Out ohasnextpage INTEGER,--output: whether there is a next page
Out ototalpages INTEGER,--output: Total pages
Out Oerror VARCHAR (100))--Output: Error message
Result SETS 1
Modifies SQL DATA
Not deterministic
LANGUAGE SQL
BEGIN
/**//*----------------------------------------------------------------
* Copyright (C) 2007 Huacius
* All rights reserved.
*
* Stored Procedure Paging
*
*
//-----------------------------------------------------------------------*/
DECLARE strSQL VARCHAR (6000);--subject sentence

DECLARE result CURSOR with return to CALLER for S2;

DECLARE exit handler for sqlexception--exception capture
BEGIN
Set oerror = ' error! ';
End;

--Body Start--
if (Iwhere <> ") Then
Set iwhere = ' WHERE ' | | Iwhere;
End If;
if (Iorder <> ") Then
Set Iorder = ' ORDER BY ' | | Iorder;
End If;
if (Ijoin <> ") Then
Set ijoin = ' ' | | Ijoin;
End If;

Set strSQL = ' SELECT count (*) from ' | | Itbname | | Ijoin | | Iwhere;
Prepare s2 from strSQL;
Open result;
Total records of fetch result into ototalrows;--
Close result;

if (ipagesize = 0) Then
Set ipagesize = 1000000;--per page display number
End If;

Set ototalpages = (ototalrows-1)/ipagesize + 1;--Total pages

if (Iocurrentpageix < 1) Then
Set Iocurrentpageix = 1;--Current page
Else
if (Iocurrentpageix > Ototalpages) Then
Set Iocurrentpageix = Ototalpages;
End If;
End If;

Set Opagestartrow = Ipagesize * (iocurrentpageix-1) + 1;--number of start per page
if (Iocurrentpageix = ototalpages) Then
Set opageendrow = ototalrows;--end of each page
Else
Set Opageendrow = Ipagesize * IOCURRENTPAGEIX;
End If;

if (Iocurrentpageix > 1) Then
Set ohaspreviouspage = 1;--whether there is a previous page
Else
Set ohaspreviouspage = 0;
End If;

if (Iocurrentpageix < ototalpages) Then
Set ohasnextpage = 1;--whether there is a next page
Else
Set ohasnextpage = 0;
End If;

Set strSQL = ' SELECT * FROM (select RowNumber () over (' | | iorder | | ') as RowNum, '
|| Ishowfield
|| ' From '
|| Itbname
|| Ijoin
|| Iwhere
|| As temp where rownum between ' | | RTrim (char (opagestartrow)) | | ' AND ' | | RTrim (char (opageendrow));
Prepare s2 from strSQL;
Open result;
--Body End--

End