Shortly before 9CBS, I have to look at the efficiency of paging on 9CBS. Many masters should not use the self-contained paging method of DataGrid without using large data, but should use the stored procedure. There are also many ways to use stored procedures, select .... top ....., not .....in ...... After considering the end, I still use SELECT .... TOP .... This stored procedure is made. The source code I use is: (according to a paragraph in the lowermost post, change it yourself, add this paragraph to get the total number of conditions) Create Procedure getPageRecords @tblname varchar (255), @fldname varchar (255), @ PageSize Int = 12, @PageIndex INT = 1, @iscount bit = 0, @ORDERTYPE BIT = 0, @strwhere varchar (1000) = '' / *
@STRSQL primary sentence @strtmp temporary variable @StrORDER Sort Type Data Source: Documents Newsletter: Yang Wenzi Date: 2004/9/21 Note: This stored process is more common, if you want to further improve efficiency, you can provide specializes in this application SELECT statement replaces select * ************************************************************* *********************************************************** ************* * / as
Declare @strsql varchar (2000) - Proficiency Declare @strtmp varchar (1000) - Temporary Variable Declare @strORDER VARCHAR (1000) - Sort Type
IF @ORDERTYPE! = 0BEGIN SET @STRTMP = "<(Select Min" set @strorder = "Order by [" @fldname "] desc" endelsebegin set @strtmp = "> (Select Max" set @strorder = "ORDER BY [" @fldname "] ASC "endset @strsql =" select top " str (@PageSize) " * from [" @TBLNAME "] where [" @fldname "] " @strtmp "([" @Fldname "]) from (select top" Str ((@ PageIndex-1) * @ PageSize) "[" @fldname "] from [" @TBLNAME "]" @strorder ") as tbltmp)" @strorder
If @strwhere! = '' set @strsql = "select top" str (@PageSize) "* from [" @TBLNAME "] where [" @fldname "]" @strtmp "([[[ " @Fldname "]) from (SELECT TOP " STR ((@ PageIndex-1) * @ PageSize) " [" @fldname "] from [" @TBLNAME "] Where (" @ Strwhere ")" @strorder ") as tbltmp) and (" @strwhere ")" @strorder
IF @PageIndex = 1begin set @strtmp = "" "if @strwhere! = '' set @strtmp =" Where (" @strwhere ") "
Set @STRSQL = "SELECT TOP" STR (@PageSize) "* from [" @TBLNAME "]" @strtmp "" @strorderendif @iscount! = 0 if @Strwhere! = 'set @ strsql = "Select Count (*) As Total from [" @TBLNAME "] Where (" @ Strwhere ")" Else Set @strsql = "Select Count (*) As Total from [" @tblname "] "
EXEC (@strsql) Go
-------------------------------------------------- -------------------------------------------------- -------------------------- Related Discussion in this: http://community.9cbs.net/expert/topicview.asp? Id = 3292678
