none
发一个通用、高效、简单、构思巧妙的分页存储过程(仅用T-Sql实现)给各位参考 RRS feed

  • 常规讨论

  • ---------------------------------------------------------------------------------
    -- 通用、高效、简单的分页存储过程(仅用T-Sql实现) TFSoft Workshop 2010
    ---------------------------------------------------------------------------------
    -- SelectTable : 表名,可以是一个带条件、带排序的Select语句
    --               例1: Students
    --               例2: (SELECT * FROM Students  WHERE ClassId='01' ORDER BY StuNo) AS QryStu
    --               例3: (SELECT (ClassId + StuName) AS Id,* FROM Students) AS QryStu
    -- PageNo      : 页号
    -- PageSize    : 页大小
    -- TableKey    : 主键,只能是一个,如有多个请合成(见例3)
    ---------------------------------------------------------------------------------
    Alter Procedure Dbo.GetPageData(
                   @SelectTable Nvarchar(4000),                
                   @PageNo      Int  = 1,                      
                   @PageSize    Int  = 30,
                   @TableKey    Nvarchar(4000)  = N'Id')
    As
       Begin
          Declare  @Sql Nvarchar(4000)
          Set @Sql = N''
          If @PageNo <= 1
             Begin
                Set @Sql = N'SELECT TOP {PageSize} * FROM {SelectTable}'
                Set @Sql = Replace(@Sql, N'{SelectTable}', @SelectTable)
                Set @Sql = Replace(@Sql, N'{PageSize}', @PageSize)
             End
          Else
            Begin
               Declare  @QryP1 Nvarchar(4000)
               Set @QryP1 = N'(SELECT TOP {RecCount} * FROM {SelectTable}) AS P1'
               Set @QryP1 = Replace(@QryP1, N'{SelectTable}', @SelectTable)
               Set @QryP1 = Replace(@QryP1, N'{RecCount}', (@PageNo * @PageSize) + '')
               Declare  @QryP2 Nvarchar(4000)
               Set @QryP2 = N'(SELECT TOP {RecCount} * FROM {SelectTable}) AS P2'
               Set @QryP2 = Replace(@QryP2, N'{SelectTable}', @SelectTable)
               Set @QryP2 = Replace(@QryP2, N'{RecCount}', ((@PageNo - 1) * @PageSize) + '')
               Set @Sql = N''
               Set @Sql = @Sql + N' SELECT P1.* FROM {QryP1} LEFT JOIN {QryP2}'
               Set @Sql = @Sql + N' ON P1.{TableKey}=P2.{TableKey}'
               Set @Sql = @Sql + N' WHERE (P2.{TableKey} IS NULL)'
               Set @Sql = Replace(@Sql, N'{QryP1}', @QryP1)
               Set @Sql = Replace(@Sql, N'{QryP2}', @QryP2)
               Set @Sql = Replace(@Sql, N'{TableKey}', @TableKey)
            End
          Exec( @Sql)
       End
    Return
    ---------------------------------------------------------------------------------
    2010年3月28日 15:04