Today, I query about SQL Server index is unintentionally found on the end of an article. I found code about the use of stored procedures to create paging. They are directly copied to the query analyzer, and I will debug one, and I will work together.
Note: The original author FREEDK The following is the author's organizer. This is a relatively optimal approach to establish a web application in a large number of data concentrations, and the page browsing is essential. This problem is a very common problem in database processing. The classic data paging method is: ADO record set paging method, that is, using ADO's own paging function (using a cursor) to implement paging. However, this paging method is only suitable for smaller data, because the cursor itself has a disadvantage: the cursor is stored in memory, which is very consumed. The cursor is built, and the relevant record is locked until the cursor is canceled. The cursor provides a means of scanning a row of row by line, generally uses a cursor to cross data, and perform different operations depending on the different data conditions. The cycle of the cursor (large data set) defined in multi-table and big tables is easy to enter a long wait or even crash. More importantly, for a very large data model, the page retrieves, if the method of loading the entire data source is very wasteful in accordance with the traditional method of loading each time, it is very wasteful. Nowadays, the popular paging method is typically the data of the block area of the page size, rather than retrieving all the data, and then performing the current row.
Source: Remove the Nth to Mth by the Publish Table: SELECT TOP M-N 1 * from Publish Where (Id Not in (SELECT TOP N-1 ID from Publish)
Store procedure:
Create Procedure Paging3 @ TBLNAME VARCHAR (255), - Table Name @StrGetfields VARCHAR (1000) = '*', - Requires Returns Colum @fldName Varchar (255) = '', - Sort Field Name @PageSize Int = 10, - Page size @PageIndex INT = 1, - Page @Docount bit = 0, - Return the total number of records, non-0 values returns to @Ordertype bit = 0, - Set the sort type, non-0 value, descending order @Strwhere Varchar (1500) = '' - Query Conditions (Note: Don't add where) AS
Declare @strsql varchar (5000) - Primary Sentrief Declare @Strtmp Varchar (110) - Temporary Variable Declare @strORDER VARCHAR (400) - Sort Type
IF @docount! = 0 Begin if @Strwhere! = 'set @strsql =' select count (*) Total from where ' @ strwhere else set @strsql =' select count (*) Total from 'end - above code It means that if @Docount passes is not 0, the total number of statistics is performed. All of the following code is @Docount to 0:
Else Begin if @ORDERTYPE! = 0 Begin set @strtmp = '<(select min' set @strorder = 'order by [' @fldname '] desc' - If @ORDERTYPE is not 0, it will perform descending, this sentence Very important! END ELSE BEGIN SET @STRTMP = '> (Select Max' Set @strorder = 'Order By [' @fldname '] ASC' END
IF @PageIndex = 1 Begin if @Strwhere! = ''
Set @strsql = 'SELECT TOP' STR (@Pagesize) '' @ strGetfields 'from [' @tblname '] where' @strwhere '' @strorder else
Set @strsql = 'SELECT TOP' STR (@PageSize) '' @ strGetfields 'from [' @tblname '] @strorder - If it is the first page, the above code is performed, this will speed up Speed ELSE BEGIN SET @strsql = 'SELECT TOP' STR (@Pagesize) '' @ strGetfields 'from [' @TBLNAME '] where [' @fldname '] @strtmp ' ( [' @Fldname ']) from (SELECT TOP ' STR ((@ PageIndex-1) * @ PageSize) ' [' @fldname '] from [' @TBLNAME '] ' @strorder ') as tbltmp)' @strorder
