[转载]支持定位当前页自定义排序的分页SQL(拒绝动态SQL)

[转载]支持定位当前页,自定义排序的分页SQL(拒绝动态SQL) – 牟向阳 – 博客园.

1,场景：根据学生编号查询，返回该学生所在班级的所有学生。支持分页、自定义排序及结果集自动定位到查询条件的学生编号所在页。

CREATE PROC [dbo].[Sp_testpagerandsorting] (@GroupID     INT,
                                            @CurrentId   INT,
                                            @TimeFrom    DATETIME,
                                            @TimeTo      DATETIME,
                                            @OrderBy     CHAR(50),
                                            @PageSize    INT,
                                            @CurrentPage INT)
AS
  SET nocount ON

  BEGIN
      DECLARE @StartNumber        INT,
              @EndNumber          INT,
              @CurrentIdRowNumber INT,
              @RecordCount        INT,
              @EndPageIndex       INT
      DECLARE @RowNumberTable TABLE (
        rownumber INT IDENTITY (1, 1),
        id        INT )

      --step 1: Build sort id list -------------------------------------------------------       
      INSERT INTO @RowNumberTable
                  (id)
      SELECT sm.id AS id
      FROM   dbo.test sm WITH (nolock)
      WHERE  indate BETWEEN Coalesce(@TimeFrom, indate) AND
                            Coalesce(@TimeTo, indate)
             AND sm.groupid = @GroupID
      ORDER  BY CASE
                  WHEN @OrderBy = 'InDate desc' THEN ( Row_number() OVER (ORDER BY indate DESC))
                  WHEN @OrderBy = 'InDate asc' THEN (Row_number() OVER (ORDER BY indate ASC))
                  WHEN @OrderBy = 'Id asc' THEN (Row_number() OVER (ORDER BY sm.id ASC))
                  WHEN @OrderBy = 'Id desc' THEN (Row_number() OVER (ORDER BY sm.id DESC))
                  WHEN @OrderBy = 'Name asc' THEN (Row_number() OVER (ORDER BY sm.name ASC))
                  WHEN @OrderBy = 'Name desc' THEN (Row_number() OVER (ORDER BY sm.name DESC) )
                END

      --step 2: Reset page index with current id -----------------------------------------  
      IF @CurrentIdNumber > 0
        BEGIN
            SELECT TOP 1 @CurrentIdRowNumber = rownumber
            FROM   @RowNumberTable
            WHERE  id = @CurrentIdNumber

            IF @CurrentIdRowNumber > 0
              BEGIN
                  IF @CurrentPage = 0
                    BEGIN
                        SET @CurrentPage = Ceiling(CAST(@CurrentIdRowNumber AS DECIMAL) / CAST (@PageSize AS DECIMAL))
                    END
              END
        END
      ELSE
        BEGIN
            IF @CurrentPage = 0
              BEGIN
                  SET @CurrentPage = 1
              END
        END

      --step 3: Set recordCount -----------------------------------------      
      SELECT @RecordCount = COUNT(1)
      FROM   @RowNumberTable

      --step 4: Calc startNumber & endNumber -----------------------------------------   
      SELECT @StartNumber = @PageSize * ( @CurrentPage - 1 ),
             @EndNumber = @PageSize * ( @CurrentPage - 1 ) + @pageSize,
             @EndPageIndex = Ceiling(CAST(@RecordCount AS DECIMAL) / CAST(@PageSize AS DECIMAL))

      IF @CurrentPage = @EndPageIndex
        BEGIN
            SET @EndNumber = @RecordCount
        END
      --step 5: Get sorted id of current page -----------------------------------------           
      ;WITH a
           AS (SELECT TOP (@EndNumber - @StartNumber) id,
                                                      rownumber
               FROM   (SELECT TOP (@EndNumber) id,
                                               rownumber
                       FROM   @RowNumberTable) AS b
               ORDER  BY rownumber DESC)
      --step 6: Return current page idList -------------------------------------------------------        
      SELECT [ID],
             [GroupID] [Name],
             [Address]
      FROM   dbo.test sm WITH(nolock)
             INNER JOIN a
               ON a.id = sm.id
      ORDER  BY a.rownumber

      -- step 7:return current page & record count ----------------------------------  
      SELECT @CurrentPage AS currentpage,
             @RecordCount AS recordcount
  END  

2,简单条件的,动态where语句(关于Like查询的动态where，建议使用笨办法做)

CREATE PROC [dbo].[Getstudentlistbycondition] @Name  NVARCHAR(20),
                                              @Class INT
AS
  SET nocount ON 
  BEGIN
      BEGIN
          SELECT [Name],
                 [class]
          FROM   [testtable]
          WHERE  [Class] = CASE
                             WHEN @Class > 0 THEN @Class ELSE [Class] END
                 AND [name] = CASE
                                WHEN @Name <> '' THEN @Name ELSE [Name] END
      END
  END