T-SQL分頁功能存儲過程

分頁功能存儲過程

ALTER PROCEDURE [dbo].[P_SplitPagesQuery]
@TablesName NVARCHAR(MAX),--表名或視圖名（只能傳單一表名）
@PK NVARCHAR(MAX)='',--主鍵（可以為空！）
@StrSort NVARCHAR(MAX)='',--排序欄位（按照這個欄位對查詢結果進行排序），不能為空
@SortType INT =0,--排序方式，預設為0（升序），1為降序
@StrWhere NVARCHAR(MAX)='',--查詢條件（即sql語句中的where後面部分）
@Fields NVARCHAR(MAX)='*',--讀取欄位（呈現出來的欄位）
@PageCurrent INT =1,--當前頁碼，預設為1
@PageSize INT =10,--頁面大小，預設為10
@PageCount INT OUTPUT,--總頁數（按照頁面大小一共分了多少頁）
@RecordCount INT OUTPUT--查詢結果的總條數
AS
/*
    返回值說明:查詢結果(表)
*/
DECLARE @SqlStr nvarchar(4000)

IF @StrWhere IS NOT NULL AND @StrWhere != ''
   BEGIN
     SET @StrWhere = ' WHERE ' + @StrWhere + ' '
   END
ELSE
   BEGIN
      SET @StrWhere = ''
   END
BEGIN
   IF @StrSort = ''
      IF @PK  IS NOT NULL AND @PK!=''
                SET @StrSort = @PK
   IF @PageCurrent < 1
      SET @PageCurrent = 1
     IF @SortType = 1 AND @StrSort!=''
            SET @StrSort = @StrSort + ' DESC '
   IF @SortType = 0 AND @StrSort!=''
            SET @StrSort = @StrSort + ' ASC '

   IF @PageCurrent = 1 --第一頁提高性能
       BEGIN
           SET @SqlStr = 'SELECT TOP ' + str(@PageSize) +' '+@Fields+ ' FROM ' + @TablesName + 
              ' AS tb ' + @StrWhere
                     IF @StrSort!=''
                            SET @SqlStr = @SqlStr + ' ORDER BY '+ @StrSort
       END
   ELSE
      BEGIN
         DECLARE @StartPos NVARCHAR(50)
         DECLARE @EndPos NVARCHAR(50)
         SET @StartPos = CONVERT(NVARCHAR(50),(@PageCurrent - 1) * @PageSize + 1)
         SET @EndPos = CONVERT(NVARCHAR(50),@PageCurrent * @PageSize)
         SET @SqlStr =  ' SELECT '+@Fields+ '
         FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@StrSort+') AS RowNum , tb.* '+'
         FROM '+@TablesName+' AS tb '+@StrWhere+') AS D
         WHERE RowNum BETWEEN '+@StartPos+' AND ' +@EndPos
                 IF @StrSort!=''
                            SET @SqlStr = @SqlStr + ' ORDER BY '+ @StrSort
      END
END

print @SqlStr
EXEC(@SqlStr)

DECLARE @SqlCount NVARCHAR(4000)

SET @SqlCount = 'SELECT @RecordCount=COUNT(*),@PageCount=CEILING((COUNT(*)+0.0)/'
                    + CAST(@PageSize AS VARCHAR)+') FROM ' + @TablesName + @StrWhere
--輸出參數賦值
EXEC SP_EXECUTESQL @SqlCount,N'@RecordCount INT OUTPUT,@PageCount INT OUTPUT',
                               @RecordCount OUTPUT,@PageCount OUTPUT

調用示例

EXEC @x =[dbo].[P_SplitPagesQuery] '表或視圖','',@sOrderBy,@nOrderTpye,@sSqlString,'*',
        @nPageNum,@nPageSize,@PageCount OUTPUT,@RecordCount OUTPUT

......