http://blog.9cbs.neet/wellknow/archive/2004/07/29/55146.aspx
/ *? Name: Spall_deletenoneuniQue? Enter: To query the table name and field list? Output:? Call:? Description: Paging display of thousands of data! - You can get 14.48 million records in 5 seconds. 1200 100 records of the page, is there? ? Author:? Iron Fist Mail:? Web site: http: //www.wellknow.net Updated:?? 20,040,610 Support: http: //bbs.wellknow.net Copyright:? Quoted please indicate the source when: Shaping the Future with thinking Wellknow.net * /
Create Procedure GetRecordfromPage ??? @TBLNAME ????? varchar (255), ?????? - Table name ??? @fldname ????? varchar (255), ?????? - Field name ??? @Pagesize ???? INT = 10, ??????????-page size ??? @PageIndex ??? INT = 1, ???????? ??? - page number ??? @iscount ????? bit = 0, ???????????-return to the total number of records, non-0 values return? ?? @ORDERTYPE??? Bit = 0, ??????????? - Set the sort type, non-zero, descending order ??? @Strwhere ???? varchar (1000) = ''? - Query Conditions (Note: Don't add WHERE AS
Declare @strsql ?? varchar (6000) ?????? - Prior Profile DECHARE @Strtmp ?? varchar (100) ??????? - Temporary Variable Declare @strorder Varchar (400) ???? ??? - Sort type
If @ORDERTYPE! = 0BEGIN ??? set @strtmp = "<(SELECT MIN" ??? set @strorder = "Order by [" @fldname "] desc" endelsebegin ??? set @strtmp = "> SELECT MAX "??? set @strorder ="] ASC "END
Set @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 " " @strorderif @PageIndex = 1begin ??? set @strtmp =" "??? if @strwhere! = '??????? set @strtmp =" where " @strwhere
??? set @strsql = "SELECT TOP" STR (@PageSize) "* from [" ??????? @tblname "]" @strtmp "" @strorderend
IF @iscount! = 0 ??? set @strsql = "Select count (*) as total from [" @tblname "]"
EXEC (@strsql)
Go
