sql 高效能預存程序分頁，sql預存程序分頁

sql 高效能預存程序分頁，sql預存程序分頁

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),     --表名    @FieldList VARCHAR(2000),    --顯示列名，如果是全部欄位則為*    @PrimaryKey VARCHAR(100),    --單一主鍵或唯一值鍵    @Where VARCHAR(4000),        --查詢條件 不含'where'字元，如id>10 and len(userid)>9    @Order VARCHAR(1000),        --排序 不含'order by'字元，如id asc,userid desc，必須指定asc或desc    --注意當@SortType=3時生效，記住一定要在最後加上主鍵，否則會讓你比較鬱悶    @SortType INT,               --定序 1:正序asc 2:倒序desc 3:多列排序方法    @RecorderCount INT,          --記錄總數 0:會返回總記錄    @PageSize INT,               --每頁輸出的記錄數    @PageIndex INT,              --當前頁數    @TotalCount INT OUTPUT,      --記返回總記錄    @TotalPageCount INT OUTPUT   --返回總頁數   AS    SET NOCOUNT ON    IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0    SET @Order = RTRIM(LTRIM(@Order))    SET @PrimaryKey = RTRIM(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 @PageIndex < 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    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,'') = ''    BEGIN    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 BY ' + @PrimaryKey + ' ASC'    END    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(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')    SET @new_order2 = REPLACE(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(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    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 --返回第一頁資料    BEGIN    SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '    + @TableName + @new_where1 + @new_order1    END    IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)  --返回最後一頁資料    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    END    ELSE    BEGIN    IF @SortType = 1  --僅主鍵正序排序    BEGIN    IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2  --正向檢索    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  --反向檢索    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    END    IF @SortType = 2  --僅主鍵反序排序    BEGIN    IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2  --正向檢索    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  --反向檢索    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    END    IF @SortType = 3  --多列排序，必須包含主鍵，且放置最後，否則不處理    BEGIN    IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0    BEGIN PRINT('ERR_02') RETURN END    IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2  --正向檢索    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  --反向檢索    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    END    END    EXEC(@Sql)        

調用方法：

       

 /// <summary>        /// 預存程序分頁        /// </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;        }