DB2 General paging Stored Procedure

SQL code
 
Create procedure "P_GETPAGEDATANEW"
(
P_TableName varchar (500), -- table name
P_NickName varchar (200), -- table nickname
P_IDName varchar (200), -- table primary key name
P_PageIndex integer, -- current page number
P_PageSize integer, -- size of each page
P_Strwhere varchar (8000 ),
OUT p_count integer -- total records
)
LANGUAGE SQL
BEGIN
DECLARE startNumber Integer; -- start record
DECLARE endNumber Integer; -- termination record
DECLARE PageLowerBound integer;
-- DECLARE StartID integer;
DECLARE strSql VARCHAR (4000 );
DECLARE mySQL VARCHAR (4000 );
DECLARE myCursor cursor with return to client for mySQL;

IF p_PageIndex <1 THEN
SET p_PageIndex = 1;
End if;
SET PageLowerBound = p_PageSize * (p_PageIndex-1 );
IF PageLowerBound <1 THEN
SET PageLowerBound = 1;
End if;

-- Query total records
SET strSql = 'select count (*) FROM (SELECT '| p_NickName |', rownumber () over (order by '| p_IDName | 'asc) AS rn from '| p_TableName | 'where' | p_Strwhere |') AS au ';
Prepare mySQL from strSql;
Open myCursor;
Fetch myCursor into p_count; -- total number of records
Close myCursor;
 
 
-- Query Result
SET startNumber = (p_PageIndex-1) * p_PageSize + 1;
SET endNumber = startNumber + p_PageSize;
-- SET strSql = 'select * FROM (SELECT '| p_NickName |', rownumber () over (order by '| p_IDName | 'asc) AS rn from '| p_TableName | 'where' | p_Strwhere |') AS au WHERE au. rn between' | char (startNumber) | 'AND' | char (endNumber-1 );
SET strSql = 'select * from (select '| p_NickName |', rownumber () over (order by '| p_IDName | ') as rownum from '| p_TableName | 'where' | p_Strwhere |') as temp WHERE rownum BETWEEN '| char (startNumber) | 'AND' | char (endNumber-1 );
Prepare mySQL from strSql;
Open myCursor;
-- Set nocount off;
END
 
 
 
Call example
 
/// <Summary>
/// System Construction page
/// 2011-12-19
/// </Summary>
/// <Param name = "statue"> system status </param>
/// <Param name = "pageIndex"> current page </param>
/// <Param name = "pageSize"> page size </param>
/// <Param name = "departId"> department ID </param>
/// <Param name = "m_disOperate"> System Construction operator entity </param>
/// <Param name = "result"> </param>
Public void GetInsAllPage (string statue, int pageIndex, int pageSize, out int count, string unitId, CUEntityINSOPerate m_insOperate, CResult result)
{
StringBuilder SQL = new StringBuilder ();//
SQL. AppendFormat ("II. INS_ID = IO. INS_OPID AND IO. INS_OPUNITID = '{0}'", unitId); // AND IO. INS_OPRANGE = 0
 
If (! String. IsNullOrEmpty (statue ))
{
SQL. AppendFormat ("AND II. INS_STATE in ({0})", statue );
}
If (m_insOperate! = Null)
{
// System name
If (! String. IsNullOrEmpty (m_insOperate.INS_NAME ))
{
SQL. AppendFormat ("AND II. INS_NAME like '% {0} %'", m_insOperate.INS_NAME );
}
// System type
If (! String. IsNullOrEmpty (m_insOperate.INS_TYPEID ))
{
SQL. AppendFormat ("AND II. INS_TYPEID = '{0}'", m_insOperate.INS_TYPEID );
}
// System status
If (m_insOperate.INS_STATE! = CommonStatus. GetProStatus (CommonStatus. ProjectStatus. Select ))
{
SQL. AppendFormat ("AND II. INS_STATE = {0}", m_insOperate.INS_STATE );
}
// Edit a department
If (! String. IsNullOrEmpty (m_insOperate.INS_OPDEPID.ToString ()))
{
SQL. AppendFormat ("AND IO. INS_OPDEPID = {0}", m_insOperate.INS_OPDEPID );
}
// Edited
If (! String. IsNullOrEmpty (m_insOperate.INS_OPUSERID ))
{
SQL. AppendFormat ("AND IO. INS_OPUSERID IN (select USERID from USERINFO where USERNAME like '% {0} %')", m_insOperate.INS_OPUSERID );
}
// Whether to review
// If (! String. IsNullOrEmpty (m_insOperate.INS_ISAUDIT.ToString ()))
//{
// SQL. AppendFormat ("AND IO. INS_ISAUDIT = '{0}'", m_insOperate.INS_ISAUDIT );
//}
// Edit Time
// If (m_insoperate.ins_opdate.to1_datestring ()! = "0001-1-1 ")
//{
// SQL. AppendFormat ("AND IO. INS_OPDATE BETWEEN TIMESTAMP ('{0}') AND TIMESTAMP ('{1}')", m_insOperate.INS_OPDATE, interval (1 ));
//}
}
 
Try
{
 
# Region insert data parameter list
DB2Parameter [] parameters = {
New DB2Parameter ("p_TableName", DB2Type. VarChar, 500 ),
New DB2Parameter ("p_NickName", DB2Type. VarChar, 200 ),
New DB2Parameter ("p_IDName", DB2Type. VarChar, 200 ),
New DB2Parameter ("p_PageIndex", DB2Type. Integer ),
New DB2Parameter ("p_PageSize", DB2Type. Integer ),
New DB2Parameter ("p_Strwhere", DB2Type. VarChar, 8000 ),
New DB2Parameter ("p_count", DB2Type. Integer)
};
Parameters [0]. Value = "institutionalinfo ii, INS_OPERATE IO ";
Parameters [1]. Value = "II. *, IO .*";
Parameters [2]. Value = "II. INS_ID ";
Parameters [3]. Value = pageIndex;
Parameters [4]. Value = pageSize;
Parameters [5]. Value = SQL. ToString ();
Parameters [0]. Direction = ParameterDirection. Input;
Parameters [1]. Direction = ParameterDirection. Input;
Parameters [2]. Direction = ParameterDirection. Input;
Parameters [3]. Direction = ParameterDirection. Input;
Parameters [4]. Direction = ParameterDirection. Input;
Parameters [5]. Direction = ParameterDirection. Input;
Parameters [6]. Direction = ParameterDirection. Output;
# Endregion
This. IDataBase. FillDataSet ("P_GETPAGEDATANEW", parameters, result. Value as CUTEntityINSOPerate );
Result. Success = true;
Count = CDataConvert. ConToValue <int> (parameters [6]. Value );
Result. CurrOperateType = EOperateType. Seach;
}
Catch (Exception ex)
{
Result. Success = false;
Result. ErrorInformation = ex. Message;
Result. CurrOperateType = EOperateType. Error;
Count = 0;
}
}
 
 
From C # ASP. NET programmers