PROCEDURE GetPageResult(nPageSize, lFirst, lLast, nOffset, cOffSet, cWhere, cOrderBy) >>LOCAL cOrderBy2, cOrderBy3, cPageSize, cTop >> >>* nPageSize = Size of the pages, in your example 50 >>* lFirst, Show first page >>* lLast, Show last page >>* nOffSet, Show from record nOffset onwards >>* cOffset, drilldown to an alphabetic character selected (could be one or more, so A,B,C or AA, AB, AC etc) >> >>* cWhere: It is assumed that the selection criteria: cWhere is constructed by an upper layer. So for example >> * WHERE State = 'State of California' AND status IN(2,3) >> * WHERE Address LIKE '%Lane%' AND State is 'NY' AND status = 1 >> >>* cOrderBy Indicates the fields on which the resultsets need to be ordered. This could be a comma delimited list >> * in this case the selection of offset would only operate on the first column in the list >> >>* It assumed that the current page location is handled by the application layer (stateless) and the page number display is assumed >>* to be handled by the upper layer. This routine is only retrieving the requested pages from the backend. >> >> >>* Getting the 50 located records. >> >>cOrderBy2 = cOrderBy >>cOrderBy3 = cOrderBy >>cPageSize = STR(nPageSize) >>cTop = cPageSize >> >>** Handling the naviation to a particular starting char >> >>cWhere = cWhere + IIF(EMPTY(cOffSet,'',' AND '+GETWORDNUM(cOrderBY,1) + " LIKE '"+ALLTRIM(cOffSet))+"%'" >> >>DO CASE >> CASE lLast >> cOrderBy2 = cOrderBy+' DESC' >> >> CASE nOffSet > 0 >> cTop = STR(nOffset+nPageSize) >> cOrderBy3 = cOrderBy3+' DESC' >>ENDCASE >> >>** SQL Statement will return at most (nPageSize records) with the total rowcount of the selection in the Totalrows column >> >>TEXT TO cSQL TEXTMERGE NOSHOW >> SELECT TOP <<cPageSize>> * FROM ( >> SELECT TOP <<cPageSize>> * FROM ( >> SELECT TOP <<cTop>> * >> FROM Customers >> WHERE <<cWhere>> >> ORDER BY <<cOrderBy2>> ) as A >> ORDER BY <<OrderBy3>>) as B >> INNER JOIN (SELECT COUNT(*) as totalrows FROM Customers WHERE <<cWhere>>) as C ON 1 = 1 >> ORDER BY <<cOrderby>> >>ENDTEXT >> >>=SQLEXEC(Applic.Sqlhandle, cSql, 'QueryResult') >>RETURN