Due to the huge data, there are often more than tens of thousands of records. Just take the data of the previous page is very necessary.
According to my actual situation, I designed the stored procedure, because the database is designed by me.
So I will consider it to easily achieve.
1. There must be a unique self-enamel ID field in the data table, (such a good place is obvious, can improve speed)
2. Considering the problem of sorting, only allow sequencing in a given field. For example, press COL1, COL2 ascending or decline, and cannot press COL1 ascending, then press COL2. Generally used is sorted by a single field.
/************************************************storage Process Name: Get the record set description of the current page: Table fields must have the self-increment identifier iDAuthor: regionMSN: HL_Net@msn.com Parameters: @tablename: Table name @fieldname: Displayed field, such as "col1, col2". All display '' or *, the default value is '' @ORDERTYPE: integer, sort 0 indicates the original data order, 1 denotes ascending order, 2 indicates descending order, the default value is 0, @ OrderField: Used to sort the field, according to @ORDertype Sort, such as "col1, col2", the default value is '' @condition: Query criteria, do not have a WHERE keyword, the air is given to '', the default value is '' @PageSize: A page record number, the default is 10 @PageIndex: The page index you want to take, the default is 1 @ pageCount: Output parameters, return to the total number of pages
Example:
DIMS_GETCURRENTPAGERECORD 'DIMS_TAF001', '', 1, '', '', 20, 4, @ PageCount Outputselect @PageCount ******************** *************************** /
Create Procedure DIMS_GETCURRENTPAGERECORD
@Tablename varchar (100), @fieldname varchar (200) = ', @OrDertype int = 0, @ORDERFIELD VARCHAR (200) =', @PageSize Int = 10, @ PageIndex Int = 1, @PageCount Int Outputas
BEGIN DECLARE @AscOrderStr VARCHAR (200) DECLARE @DescOrderStr VARCHAR (200) DECLARE @ConditionStr VARCHAR (1000) DECLARE @FieldNameStr VARCHAR (200) DECLARE @MaxRowIndex INT DECLARE @GetRowsCount INT DECLARE @SelectStr VARCHAR (3000)
- Construct Sort strings, positive sequence and reverse order ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------ if (@OrDertype = 0) Begin set @AScorderstr = 'id' set @Descorderstr = 'id desc' end else if (@ordertype = 1) begin if (@Orderfield = ') begin set @ Ascorderstr = 'id' set @descorderstr = 'id desc' end else begin set @AScorderstr = @Oscorderstr = @ostr = @os @Descorderstr = Replace (@ORDERFIELD, ',', 'DESC,') 'DESC' END ELSE IF @OrderType = 2) BEGIN IF (@OrderField = '') BEGIN SET @DescOrderStr = 'ID' SET @AscOrderStr = 'ID DESC' END ELSE BEGIN SET @DescOrderStr = @OrderField; SET @AscOrderStr = REPLACE (@OrderField, ' , ',' Desc, ') ' descorderstr! = ') Set @AScorderstr =' Order by ' @ascorderstr if (@Descorderstr! =') Set @Descorderstr = 'Order By' @Descorderstr
- Construct conditional statement, maximum number of lines, display fields, number of lines available --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ IF (@condition! = '') Set @conditionstr = 'Where' @condition else set @conditionstr = @condition
SET @MaxRowIndex = @ PageSize * @ PageIndex IF (@FieldName = '' OR @FieldName = '*') SET @FieldNameStr = '*' ELSE SET @FieldNameStr = '' @ FieldName '' DECLARE @TableRowsCount INT
Create Table #TB (Rounce "
INSERT INTO #TB EXEC ('SELECT Count (*) from' @ TableName) Select @TableRowsCount = Rounce #tb #tbdrop Table #TB
IF (@TableRowsCount <@MaxRowIndex) BEGIN SET @GetRowsCount = @PageSize - (@ MaxRowIndex - @TableRowsCount) IF (@GetRowsCount <0) SET @GetRowsCount = 0 END ELSE SET @GetRowsCount = @PageSize
- Calculate the total number of pages (@TableRowscount = 0) set @PageCount = 1 else begin set @PageCount = ceiling (@ TableRowscount / @ Pagesize) End
- Construct query statement, execute statement ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---
SET @SelectStr = 'SELECT TOP' CAST (@GetRowsCount AS VARCHAR) @ FieldNameStr 'FROM (SELECT TOP' CAST (@GetRowsCount AS VARCHAR) '* FROM (SELECT TOP' CAST (@GetRowsCount AS VARCHAR) '* FROM (SELECT TOP' CAST (@MaxRowIndex AS VARCHAR) '* FROM' @ TableName @ ConditionStr @ AscOrderStr ') DERIVEDTBL' @ DescOrderStr ') DERIVEDTBL' @ AscOrderStr ') DERIVEDTBL' --PRINT @SelectStr
EXEC (@selectstr)
End
Go
