步步为营：SQL通用存储过程分页

这个通用分页维护了好久，也经过需求变化不断的修改.小弟不才,写得不够好..请大虾莫喷..

放出来纯属交流学习，当然分页方式还有好多好多，我们可以讨论性能和优化，帮助自己不断的提高。

第一种使用NOTIN的方式进行分页。

USE [Data]
GO
/****** Object:  StoredProcedure [dbo].[P_CommonPager_NotIn]    Script Date: 11/22/2011 16:24:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:           薛凯
-- Create date: 2011年11月22日
-- Description:   通用存储过程分页 NOT IN 方式
-- =============================================
IF EXISTS (SELECT 1 FROM  SYSOBJECTS WHERE  id = object_id('P_CommonPager_RowNomber') AND   TYPE = 'P')
   DROP PROC P_MoveShikeeChange
GO
CREATE PROCEDURE [dbo].[P_CommonPager_NotIn] --P_CommonPager_NotIn 'View_TrySpr','View_TrySpr.*','tid','order by num desc','and trybrand=1',2,10,4444,1
    @tblName   VARCHAR(255)='sk_Users',            -- 表名如：'xtest'
    @strGetFields VARCHAR(1000) = '*',  -- 需要返回的列如：'xname,xdemo'
    @pkName        NVARCHAR(50)='uid',   -- 主键名
    @strOrder VARCHAR(255)='order by uid desc',          -- 排序的字段名如：'order by id desc'
    @strWhere  VARCHAR(max) = '1=1',      -- 查询条件(注意:不要加where)如:'xname like ''%222name%''' 
    @pageIndex  INT = 1,                -- 页码如：2
    @pageSize   INT = 20,               -- 每页记录数如：20
    @recordCount INT=0 OUTPUT,             -- 记录总数
    @doCount INT=0                      -- 非0则统计,为0则不统计(统计会影响效率)
AS
    DECLARE @sql NVARCHAR(MAX)
    DECLARE @sqltemp  NVARCHAR(MAX)
    DECLARE @strCount NVARCHAR(MAX)
    DECLARE @pageCount NVARCHAR(MAX)
    SET @sql=' From '+@tblName
BEGIN
    
    IF(@strWhere!='') 
        SET @sql=@sql+' Where '+@strWhere
    --总记录条数
    IF(@doCount!=0)
    BEGIN
        IF(@strWhere !='')
        BEGIN
            SET @strCount='set @recordCount=(select count(1) from '+ @tblName + ' where '+@strWhere+' )'
        END
        ELSE
        BEGIN
            SET @strCount='set @recordCount=(select count(1) from '+ @tblName + ' )'
        END
        print @strCount
        EXECUTE sp_executesql @strCount ,N'@recordCount INT output',@recordCount OUTPUT
    END
    
    --计算出总页数
    SET @pageCount = @recordCount / @pageSize
    IF(@recordCount % @pageSize != 0) 
    BEGIN
        SET @pageCount = @pageCount + 1
    END
    --如果查询页数大于总页数
    IF(@PageIndex > @pageCount)
    BEGIN
        SET @PageIndex = @pageCount
    END

    IF @strWhere !=''
    BEGIN
        SET @strWhere=' where '+@strWhere
    END

    IF (@PageIndex>1)
    BEGIN
        SET @sqltemp=@PKName+' not in (Select Top '+CAST((@PageSize*(@PageIndex-1)) AS NVARCHAR)+''+@PKName+''+@sql
        IF(@strOrder!='')SET @sqltemp=@sqltemp+''+@strOrder
        SET @sqltemp=@sqltemp+')'
        SET @sql='Select Top '+CAST(@PageSize AS NVARCHAR)+''+@strGetFields+''+@sql 
        IF(@strWhere!='')
            SET @sql=@sql+' And '+@sqltemp
        ELSE 
            SET @sql=@sql+' Where '+@sqltemp
    END
    ELSE
    BEGIN
        SET @sql='Select Top '+CAST(@PageSize AS NVARCHAR)+''+@strGetFields+''+@sql 
    END
    IF(@strOrder!='') 
        SET @sql=@sql+''+@strOrder
    PRINT @sql
    EXEC(@sql)
END

第二种方式是使用RowNomber进行分页。我比较喜欢使用这种，速度比NOTIN要快（推荐）

USE [Data]
GO
/****** Object:  StoredProcedure [dbo].[P_CommonPager_RowNomber]    Script Date: 11/22/2011 16:26:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:          薛凯
-- Create date: 2011年11月22日
-- Description:  通用存储过程分页 ROW_NUMBER 方式
-- =============================================
IF EXISTS (SELECT 1 FROM  SYSOBJECTS WHERE  id = object_id('P_CommonPager_RowNomber') AND   TYPE = 'P')
   DROP PROC P_MoveShikeeChange
GO
CREATE PROCEDURE [dbo].[P_CommonPager_RowNomber]
@tblName   VARCHAR(255)='sk_Users',            -- 表名如：'xtest'
@strGetFields VARCHAR(1000) = '*',  -- 需要返回的列如：'xname,xdemo'
@strOrder VARCHAR(255)='order by uid desc',          -- 排序的字段名如：'order by id desc'
@strWhere  VARCHAR(max) = '1=1',      -- 查询条件(注意:不要加where)如:'xname like ''%222name%''' 
@pageIndex  INT = 1,                -- 页码如：2
@pageSize   INT = 20,               -- 每页记录数如：20
@recordCount INT=0 output,             -- 记录总数
@doCount INT=0                      -- 非0则统计,为0则不统计(统计会影响效率)
AS
DECLARE @strSQL    NVARCHAR(MAX)
DECLARE @strCount NVARCHAR(MAX)
DECLARE @pageCount NVARCHAR(MAX)
BEGIN
    --总记录条数
    IF(@doCount!=0)
    BEGIN
        IF(@strWhere !='')
        BEGIN
            SET @strCount='set @num=(select count(1) from '+ @tblName + ' where '+@strWhere+' )'
        END
        ELSE
        BEGIN
            SET @strCount='set @num=(select count(1) from '+ @tblName + ' )'
        END
        EXECUTE sp_executesql @strCount ,N'@num INT output',@recordCount OUTPUT
    END
    
    --计算出总页数
    SET @pageCount = @recordCount / @pageSize
    IF(@recordCount % @pageSize != 0) 
    BEGIN
        SET @pageCount = @pageCount + 1
    END
    --如果查询页数大于总页数
    IF(@PageIndex > @pageCount)
    BEGIN
        SET @PageIndex = @pageCount
    END
    
    IF @strWhere !=''
    BEGIN
        SET @strWhere = ' where ' + @strWhere
    END
    SET @strSQL = 'SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'
 
    SET @strSQL = @strSQL + @strGetFields + ' FROM '+@tblName+''+ @strWhere
 
    SET @strSQL = @strSQL+') AS sp WHERE ROWID BETWEEN ' + STR((@PageIndex-1) * @PageSize + 1)
 
    SET @strSQL = @strSQL + ' AND ' + STR(@PageIndex*@PageSize)
    PRINT @strSQL
    EXEC (@strSQL)
END

下面再写出配合昨天我修改的分页查询按每段100页的分页导航算法

       #region 淘宝论坛分页方式
         ///<summary>
        /// 淘宝论坛分页方式
         ///</summary>
        ///<param name="sumCount">总条数</param>
        ///<param name="pageSize">每页大小</param>
        ///<param name="thisIndex">当前页</param>
        ///<param name="webstr">分页字符串page=[$ToPage$]</param>
        ///<returns></returns>
        public static string BindPageText10(int sumCount, int pageSize, int thisIndex, string webstr) 
        {
            StringBuilder strHtml = new StringBuilder();
            int endPage = 0;
            int sumPage = 0;
            //计算总页数，获得总页数
            int pageCount = sumCount / pageSize;
            //如果不能正常取余就给总页数 加1
            if (sumCount % pageSize != 0)
                pageCount += 1;//获得
            //如果输入的页数大于总页数那么页数就是总页数
            if (thisIndex > pageCount)
                thisIndex = pageCount; 
            //按100分段总页数，
            sumPage = pageCount / 100;
            for (int i = 1; i <= sumPage; i++)
            {
                if (thisIndex < i * 100)
                {
                    pageCount = i * 100;
                    break;
                }
            }
            //如果当前页 等于零 就设定当前页是 第一页
            if (thisIndex == 0)
            {
                thisIndex = 1;
            }
            //如果当前页大于第1页的都显示 [上一页]按钮 给用户点击 
            //实现 [上一页]
            if (thisIndex > 1)
            {
                strHtml.Append("<a href=\"");
                strHtml.Append(webstr.Replace("[$ToPage$]", Convert.ToString(thisIndex - 1)));
                strHtml.Append("\" title=\"上一页\" class=\"prev\"><b class=\"trigon\"></b><span>上一页</span></a>");
            }
            //如果当前页大于第1页 总页数也大于1页
            //实现 [上一页] [1]
            if (thisIndex > 1 && pageCount > 1)
            {
                strHtml.Append("<a href=\"");
                strHtml.Append(webstr.Replace("[$ToPage$]", "1"));
                strHtml.Append("\" title=\"当前: 1\">1</a>");
            }
            //如果当前页大于第2页 总页数也大于2页
            //实现 [上一页] [1][2]
            if (thisIndex > 2 && pageCount > 2)
            {
                strHtml.Append("<a href=\"");
                strHtml.Append(webstr.Replace("[$ToPage$]", "2"));
                strHtml.Append("\" title=\"当前: 2\">2</a>");
            }
            //如果当前页大于等于第6页
            if (thisIndex >= 6)
            {
                strHtml.Append("<span class=\"etc\">...</span>");
            }
            //如果总页数 大于 当前页+两页之后的页数
            //否则末尾页就是总页数
            if (pageCount > thisIndex + 2)
                endPage = thisIndex + 2;
            else
                endPage = pageCount;
            //循环遍历 假如当前页为 第6页 
            //实现 [上一页] [1][2]...[4][5] 6 [7][8]
            //中间页数 [4][5] 6 [7][8]
            for (int i = thisIndex - 2; i <= endPage; i++)
            {
                if (i > 0)
                {
                    if (i == thisIndex)
                        strHtml.Append("<a href=\"javascript:;\" class=\"sel\">" + i + "</a>");//当前选中页
                    else
                    {
                        if (i != 1 && i != pageCount && i != 2 && i != pageCount - 1)
                        {
                            strHtml.Append("<a href=\"");
                            strHtml.Append(webstr.Replace("[$ToPage$]", Convert.ToString(i)));
                            strHtml.Append("\" title=\"当前: ");
                            strHtml.Append(i);
                            strHtml.Append("\">");
                            strHtml.Append(i);
                            strHtml.Append("</a>");
                        }
                        else
                        {
                            //解决是第一页的时候不显示第二页的导航
                            //解决是最后页的时候不显示倒数第二页的导航
                            if (thisIndex == 1 || thisIndex == pageCount)
                            {
                                strHtml.Append("<a href=\"");
                                strHtml.Append(webstr.Replace("[$ToPage$]", Convert.ToString(i)));
                                strHtml.Append("\" title=\"当前: ");
                                strHtml.Append(i);
                                strHtml.Append("\">");
                                strHtml.Append(i);
                                strHtml.Append("</a>");
                            }
                        }
                    }
                }
            }
            //如果当前页 +3页之后的页数小于总页数
            //实现 [上一页] [1][2]...[4][5] 6 [7][8]...
            if (thisIndex + 3 < pageCount)
                strHtml.Append("<span class=\"etc\">...</span>");
            //如果当前页 不是最后一页 - 1 之后的页数
            //实现 [上一页] [1][2]...[4][5] 6 [7][8]...[99]
            if (thisIndex != pageCount - 1 && pageCount - 1 != 0 && thisIndex < pageCount)
            {
                strHtml.Append("<a href=\"");
                strHtml.Append(webstr.Replace("[$ToPage$]", Convert.ToString(pageCount - 1)));
                strHtml.Append("\" title=\"当前: ");
                strHtml.Append(pageCount - 1);
                strHtml.Append("\">");
                strHtml.Append(pageCount - 1);
                strHtml.Append("</a>");
            }
            //如果当前页 不是最后一页 
            //实现 [上一页] [1][2]...[4][5] 6 [7][8]...[99][100]
            if (thisIndex != pageCount && pageCount != 0)
            {
                strHtml.Append("<a href=\"");
                strHtml.Append(webstr.Replace("[$ToPage$]", Convert.ToString(pageCount)));
                strHtml.Append("\" title=\"当前: ");
                strHtml.Append(pageCount);
                strHtml.Append("\">");
                strHtml.Append(pageCount);
                strHtml.Append("</a>");
            }
            if (thisIndex + 1 <= pageCount)
            {
                strHtml.Append("<a href=\"");
                strHtml.Append(webstr.Replace("[$ToPage$]", Convert.ToString(thisIndex + 1)));
                strHtml.Append("\" title=\"下一页\" class=\"next\"><span>下一页</span><b class=\"trigon\"></b></a>");
            }
            //strHtml.Append("<span class=\"go-page\"><em>共" + pageCount + "页</em>");
            strHtml.Append("</span>");
            string str = strHtml.ToString();
            return strHtml.ToString();
        }
        #endregion

模仿导航http://bbs.taobao.com/catalog/459501.htm?spm=1.46102.147302.28&