SQL high-performance Stored Procedure paging and SQL stored procedure Paging

SQL high-performance Stored Procedure paging and SQL stored procedure Paging

USE [Lyjjr] GO/****** Object: StoredProcedure [dbo]. [P_ViewPage] Script Date: 05/29/2015 17:18:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ongo alter proc [dbo]. [P_ViewPage] @ TableName VARCHAR (200), -- table name @ FieldList VARCHAR (2000), -- display the column name. If it is all fields, it is * @ PrimaryKey VARCHAR (100 ), -- single primary key or unique value Key @ Where VARCHAR (4000), -- the query condition does not contain the 'where' character, such as id> 10 and len (userid)> 9 @ Order VARCHAR (1000), -- sorting does not contain the 'ORDER BY' character, as shown in figure Id asc, userid desc, must specify asc or desc -- note that it takes effect when @ SortType = 3. Remember to add a primary key at the end; otherwise, it will make you depressed @ SortType INT, -- sorting Rule 1: Positive asc 2: reverse desc 3: Multi-column sorting method @ RecorderCount INT, -- total number of records 0: Total records @ PageSize INT will be returned, -- number of records OUTPUT per page @ PageIndex INT, -- current page number @ TotalCount int output, -- Record total returned records @ TotalPageCount int output -- total number of returned pages as set nocount on if isnull (@ TotalCount ,'') = ''set @ TotalCount = 0 SET @ Order = RTRIM (LTRIM (@ Order) SET @ PrimaryKey = RTR IM (LTRIM (@ PrimaryKey) SET @ FieldList = REPLACE (RTRIM (LTRIM (@ FieldList), '','') while charindex (',', @ Order)> 0 or charindex (',', @ Order)> 0 begin set @ Order = REPLACE (@ Order, ') SET @ Order = REPLACE (@ Order, ',') end if isnull (@ TableName, '') ='' or isnull (@ FieldList, '') ='' or isnull (@ PrimaryKey, '') ='' OR @ SortType <1 OR @ SortType> 3 OR @ RecorderCount <0 OR @ PageSize <0 OR @ Pag EIndex <0 begin print ('err _ 00') return end if @ SortType = 3 begin if (UPPER (RIGHT (@ Order, 4 ))! = 'Asc 'and upper (RIGHT (@ Order, 5 ))! = 'Desc') begin print ('err _ 02 ') return end declare @ new_where1 VARCHAR (2000) DECLARE @ new_where2 VARCHAR (2000) DECLARE @ new_order1 VARCHAR (1000) DECLARE @ new_order2 VARCHAR (1000) DECLARE @ new_order3 VARCHAR (1000) DECLARE @ SQL VARCHAR (8000) DECLARE @ SqlCount NVARCHAR (4000) IF ISNULL (@ where ,'') = ''in in SET @ new_where1 = ''SET @ new_where2 = 'where' end else begin set @ new_where1 = 'where' + @ where set @ new_where2 = 'where' + @ WHERE + 'and' end if isnull (@ order, '') = ''OR @ SortType = 1 OR @ SortType = 2 begin if @ SortType = 1 begin set @ new_order1 = 'ORDER BY' + @ PrimaryKey + 'asc 'set @ new_order2 =' order by '+ @ PrimaryKey + 'desc' end if @ SortType = 2 begin set @ new_order1 = 'ORDER BY' + @ PrimaryKey + 'desc' SET @ new_order2 = 'ORDER' + @ PrimaryKey + 'asc 'end else begin set @ new_order1 = 'ORDER BY' + @ order end if @ SortType = 3 and charindex (', '+ @ PrimaryKey + '',', '+ @ Order)> 0 begin set @ new_order1 = 'ORDER BY' + @ order set @ new_order2 = @ Order + ', 'set @ new_order2 = REPLACE (@ new_order2, 'asc, ',' {ASC}, '), 'desc',' {DESC },') SET @ new_order2 = REPLACE (@ new_order2, '{ASC},', 'desc'), '{DESC},', 'asc ,') SET @ new_order2 = 'ORDER BY' + SUBSTRING (@ new_order2, 1, LEN (@ new_order2)-1) IF @ FieldList <> '*' begin set @ new_order3 = REPLACE (@ Order + ',', 'asc, '), 'desc ,', ',') SET @ FieldList = ',' + @ FieldList while charindex (',', @ new_order3)> 0 begin if charindex (SUBSTRING (',' + @ new_order3, 1, CHARINDEX (',', @ new_order3), ',' + @ FieldList + ',')> 0 begin set @ FieldList = @ FieldList + ', '+ SUBSTRING (@ new_order3, 1, CHARINDEX (', ', @ new_order3) end set @ new_order3 = SUBSTRING (@ new_order3, CHARINDEX (', ', @ new_order3) + 1, LEN (@ new_order3) end set @ FieldList = SUBSTRING (@ FieldList, 2, LEN (@ FieldList )) end set @ SqlCount = 'select @ TotalCount = COUNT (*), @ TotalPageCount = CEILING (COUNT (*) + 0.0)/'+ CAST (@ PageSize as varchar) + ') FROM' + @ TableName + @ new_where1 IF @ RecorderCount = 0 begin exec SP_EXECUTESQL @ SqlCount, n' @ TotalCount int output, @ TotalPageCount INT output', @ TotalCount OUTPUT, @ TotalPageCount output end else begin select @ TotalCount = @ RecorderCount end if @ PageIndex> CEILING (@ TotalCount + 0.0)/@ PageSize) begin set @ PageIndex = CEILING (@ TotalCount + 0.0)/@ PageSize) end if @ PageIndex = 1 OR @ PageIndex> = CEILING (@ TotalCount + 0.0)/@ PageSize) begin if @ PageIndex = 1 -- returns the first page of data begin set @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from' + @ TableName + @ new_where1 + @ new_order1 end if @ PageIndex> = CEILING (@ TotalCount + 0.0)/@ PageSize) -- returns the last page of data begin set @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from ('+ 'select TOP' + STR (ABS (@ PageSize * @ PageIndex-@ TotalCount-@ PageSize )) + ''+ @ FieldList + 'from' + @ TableName + @ new_where1 + @ new_order2 + ') as tmp '+ @ new_order1 end else begin if @ SortType = 1 -- only the primary key is sorted in positive order begin if @ PageIndex <= CEILING (@ TotalCount + 0.0)/@ PageSize) /2 -- forward query begin set @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from' + @ TableName + @ new_where2 + @ PrimaryKey + '>' + '(select max (' + @ PrimaryKey + ') FROM (select top '+ STR (@ PageSize * (@ PageIndex-1) + ''+ @ PrimaryKey + 'from' + @ TableName + @ new_where1 + @ new_order1 + ') as tmp) '+ @ new_order1 end else -- reverse retrieval begin set @ SQL = 'select top' + STR (@ PageSize) + ''+ @ FieldList + 'from ('+ 'select TOP' + STR (@ PageSize) + ''+ @ FieldList + 'from' + @ TableName + @ new_where2 + @ PrimaryKey + '<' + '(select min (' + @ PrimaryKey + ') FROM (select top '+ STR (@ TotalCount-@ PageSize * @ PageIndex) + ''+ @ PrimaryKey + 'from' + @ TableName + @ new_where1 + @ new_order2 + ') as tmp) '+ @ new_order2 + ') as tmp '+ @ new_order1 end if @ SortType = 2 -- only the primary key is sorted in reverse order begin if @ PageIndex <= CEILING (@ TotalCount + 0.0)/@ PageSize) /2 -- forward query begin set @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from' + @ TableName + @ new_where2 + @ PrimaryKey + '<' + '(select min (' + @ PrimaryKey + ') FROM (select top '+ STR (@ PageSize * (@ PageIndex-1) + ''+ @ PrimaryKey + 'from' + @ TableName + @ new_where1 + @ new_order1 + ') as tmp) '+ @ new_order1 end else -- reverse retrieval begin set @ SQL = 'select top' + STR (@ PageSize) + ''+ @ FieldList + 'from ('+ 'select TOP' + STR (@ PageSize) + ''+ @ FieldList + 'from' + @ TableName + @ new_where2 + @ PrimaryKey + '>' + '(select max (' + @ PrimaryKey + ') FROM (select top '+ STR (@ TotalCount-@ PageSize * @ PageIndex) + ''+ @ PrimaryKey + 'from' + @ TableName + @ new_where1 + @ new_order2 + ') as tmp) '+ @ new_order2 +') as tmp '+ @ new_order1 end if @ SortType = 3 -- Multi-column sorting, which must contain the primary key and be placed at the END, otherwise, do not process begin if charindex (',' + @ PrimaryKey + '', ',' + @ Order) = 0 begin print ('err _ 02 ') return end if @ PageIndex <= CEILING (@ TotalCount + 0.0)/@ PageSize)/2 -- forward retrieval begin set @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from ('+ 'select TOP' + STR (@ PageSize) + ''+ @ FieldList + 'from ('+ 'select TOP' + STR (@ PageSize * @ PageIndex) + ''+ @ FieldList + 'from' + @ TableName + @ new_where1 + @ new_order1 + ') as tmp' + @ new_order2 + ') as tmp '+ @ new_order1 end else -- reverse retrieval begin set @ SQL = 'select top' + STR (@ PageSize) + ''+ @ FieldList + 'from ('+ 'select TOP' + STR (@ PageSize) + ''+ @ FieldList + 'from ('+ 'select TOP' + STR (@ PageSize * @ PageIndex) + ''+ @ FieldList + 'from' + @ TableName + @ new_where1 + @ new_order1 + ') as tmp' + @ new_order2 + ') as tmp '+ @ new_order1 end exec (@ SQL)

Call method:

/// <Summary> /// stored procedure page /// </summary> /// <param name = "pageIndex"> </param> /// <param name = "pageSize"> </param> // <param name = "where"> </param> // <param name = "order"> </param>/ // <param name = "sortType"> </param> // <param name = "totalCount"> </param> // <returns> </returns> public dataTable GetLineRoadPage (int pageIndex, int pageSize, string where, string order, int sortType, out int totalCount) {totalCount = 0; SqlParameter [] parameters = {new SqlParameter ("@ TableName", SqlDbType. varChar, 200), new SqlParameter ("@ FieldList", SqlDbType. varChar, 2000), new SqlParameter ("@ PrimaryKey", SqlDbType. varChar, 100), new SqlParameter ("@ Where", SqlDbType. varChar, 2000), new SqlParameter ("@ Order", SqlDbType. varChar, 1000), new SqlParameter ("@ SortType", SqlDbType. int, 4), new SqlParameter ("@ RecorderCount", SqlDbType. int, 4), new SqlParameter ("@ PageSize", SqlDbType. int, 4), new SqlParameter ("@ PageIndex", SqlDbType. int, 4), new SqlParameter ("@ TotalCount", SqlDbType. int, 4), new SqlParameter ("@ TotalPageCount", SqlDbType. int, 4)}; parameters [0]. value = "vLineRoad"; parameters [1]. value = "[LrID], [Title], [SettlePrice], [MarketPrice], [ImageUrls], [LineType], [TraficTool], [NeedDays], [IsShow], [IsRecommend], [ThemID], [SplID], [LinePriceType], [IsUseTicket], [NeedNights], [ProductCode], [ChildrenPrice], [CreateTime], [UpdateTime], [SpLnName], [SupplierName], [Tel], [Dates] "; parameters [2]. value = "LrID"; parameters [3]. value = where; parameters [4]. value = order; parameters [5]. value = sortType; parameters [6]. value = 0; parameters [7]. value = pageSize; parameters [8]. value = pageIndex; parameters [9]. direction = ParameterDirection. output; parameters [10]. direction = ParameterDirection. output; DataTable dt = DbHelperSQL. runProcedureToDataTable ("P_ViewPage", parameters); totalCount = Convert. toInt32 (parameters [9]. value); return dt ;}