Kỹ thuật phân trang bằng Store Procedure

Bài này mình giới thiệu thuật toán trang bằng SQL với những tùy chọn hiển thị khác nhau.

Ở bài viết Phân trang trong SQL mình đã giới thiệu cách dùng Store để phân trang, Ở bài này mình sẽ giới thiệu thuật toán phân trang tối ưu hơn.
Giả sử bạn muốn trang web của bạn phân trang theo cách hiển thị như sau
Trang đầu Trang trước 2 3 [4] 5 6 Trang sau Trang cuối (Với số trang hiển thị =5 và đang hiển thị nội dung trang thứ 4, và mỗi trang 10 bản ghi)

1. Store trả về chuỗi hiển thị phân trang.
 Trước tiên bạn cần viết 1 hàm trả về chuỗi hiển thị phân trang. Hàm này có các biến truyền vào là:
@Total=Tổng số bản ghi truy vấn
@currPage=Trang hiện hàng
@PageSize=Số trang muốn hiển thị
@rowperpage = Số bản ghi trên 1 trang

Kết quả trả về của store này là chuỗi (html) hiển thị phân trang có dạng như sau:

EXEC [spPhanTrangSQL] 56,4,5, 10 (Demo với @Total=56, @currPage=4, @Pagesize=5, @rowperpage=10)
<a href="?page=1">Trang đầu</a> 
<a href="?page=3">Trang trước</a>
<a href="?page=2">2</a> 
<a href="?page=3">3</a> 
[4] 
<a href="?page=5">5</a> 
<a href="?page=6">6</a> 
<a href="?page=5">Trang sau</a> 
<a href="?page=6">Trang cuối</a>

 Store được viết như sau:

CREATE PROCEDURE [dbo].[spPhanTrangSQL]
@Total int,
@currPage int ,
@PageSize int,
@rowperpage int  
AS
BEGIN
DECLARE  @PageNumber int SET @PageNumber=1
DECLARE @i int
SET @i=1
DECLARE @TotalPage int
IF @Total%@rowperpage>0
SET @TotalPage=(@Total/@rowperpage)+1
ELSE
SET @TotalPage=@Total/@rowperpage 
DECLARE @Start int SET @Start=0
DECLARE @SQL nvarchar(4000)
SET @SQL=''
IF @currPage<=@TotalPage
BEGIN
    -- Xử lý trường hợp @currPage=1
    IF @currPage=1
    BEGIN
        SET @SQL=@SQL+ N'Trang '
        SET @PageNumber=@PageSize
        IF @PageNumber>@TotalPage SET @PageNumber=@TotalPage
        SET @Start=1
    END
    ELSE
    BEGIN
        SET @SQL=@SQL+ N' <a href="?page=1">Trang đầu</a>'
        SET @SQL=@SQL+ ' <a href="?page='+ 
            Cast((@currPage-1) AS nvarchar(4))+N'">Trang trước</a>'
        -- Xử lý trường hợp (@TotalPage-@currPage)<@PageSize/2
        IF(@TotalPage-@currPage)<@PageSize/2
           BEGIN
              SET @Start=(@TotalPage-@PageSize)+1
              IF @Start<0 SET @Start=1 
              SET @PageNumber = @TotalPage
           END
        ELSE
        BEGIN
            IF (@currPage-(@PageSize/2))=0
            BEGIN
                SET @Start=1
                SET @PageNumber=@currPage+(@PageSize/2)+1
                IF @TotalPage<@PageNumber
                    SET @PageNumber=@TotalPage
            END
            ELSE
               BEGIN
                  SET @Start=@currPage-(@PageSize/2)
                  IF @Start<0 SET @Start=1 
                  SET @PageNumber=@currPage+(@PageSize/2)
                  IF @TotalPage<@PageNumber
                      SET @PageNumber=@TotalPage
                  ELSE
                  IF @PageNumber <@PageSize 
                      SET @PageNumber=@PageSize
               END
        END
     END    

    SET @i=@Start
    WHILE @i<=@PageNumber
    BEGIN
        IF @i=@currPage
            SET @SQL=@SQL+'
             ['+Cast(Cast(@i AS int) AS nvarchar(4))+'] '
        ELSE
            SET @SQL=@SQL+'
             <a href="?page='+Cast(@i AS nvarchar(4))+'">'
                +Cast(@i AS nvarchar(4))+'</a> '
        SET @i=@i+1 
    END
    IF @currPage<@TotalPage
    BEGIN
        SET @SQL=@SQL+ N'
         <a href="?page='+Cast((@currPage+1) 
            AS nvarchar(4))+N'">Trang sau</a>'
         SET @SQL=@SQL+ N' 
             <a href="?page='+cast(@TotalPage AS nvarchar(6))+
              N'">Trang cuối</a>'
    END
    SELECT @SQL AS PhanTrang    
    -- PRINT @SQL
END
END

2. Store phân trang

Tiếp theo chúng ta sẻ dùng thủ tục trên vào việc phân trang dữ liệu. Thủ tục này  có các biến truyền vào:
@currpage=Trang hiện hành
@recodeperpage=Số bản ghi trên mỗi trang

@Pagesize=Số trang hiển thị phân trang
Thủ tục sẽ trả về dữ liệu là 2 bảng (Dùng dataset).

--CREATE BY webmaster@hmweb.com.vn
CREATE PROCEDURE [dbo].[spTB_TableName_PhanTrang]
@currPage int,
@recodperpage int,
@Pagesize int
AS
Begin
    Begin
    WITH s AS
    (
        SELECT ROW_NUMBER() 
            OVER(ORDER BY MaTruong,TenTruong) AS RowNum, 
            MaTruong, TenTruong
        FROM dbo.TB_TableName  
    )
    Select * From s 
    Where RowNum Between 
        (@currPage - 1)*@recodperpage+1 
            AND @currPage*@recodperpage
    END
    -- Tính tổng số bản ghi
    DECLARE @Tolal int
    SELECT @Tolal=Count(*) FROM TB_TableName

    EXEC spPhanTrangSQL @Tolal, @currPage,@Pagesize, 10
End

Sử dụng Store này bạn cần bind dữ liệu và Fill vào 1 DataSet, Sau đó thực hiện hiển thị dữ liệu phân trang. Bạn đọc bài viết Kỹ thuật phân trang bằng Store trong asp.net sẽ hướng dẫn bạn cách sử dụng Store trên