SQL 分頁查詢 SP 分享

GridView查詢在資料來源不會自動做分頁，因此需要靠分頁語法或分頁SP來幫忙做分頁查詢，加上GridView的底層修改，可以每次只查到真正需要的資料，請參考下面分頁查詢SP範例：



CREATE PROCEDURE [dbo].[usp_QueryByPager] 
    @PageIndex INT = 1,
    @PageSize INT = 10,
    @QuerySQL NVARCHAR(MAX) = NULL,
    @OrderBy NVARCHAR(1000) = NULL,
    @Paging CHAR(1) = 'Y',
    @ReturnCount INT = 0 OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ExecSQL NVARCHAR(MAX),@startRowIndex INT, @maximumRows INT, @FromIndex INT;


    SET @startRowIndex = @PageIndex * @PageSize;
    SET @maximumRows = @PageSize; 
    
    IF @Paging='Y'
    BEGIN
SET @ExecSQL = 'SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RankNumber  
FROM (' + @QuerySQL + '
) as Main
) AS Result
WHERE RankNumber > ' + CONVERT(VARCHAR(10), @startRowIndex) + ' 
AND RankNumber <= ' + CONVERT(VARCHAR(10), @startRowIndex+@maximumRows) 
+ ' ORDER BY RankNumber'
END
ELSE
BEGIN
SET @ExecSQL = @QuerySQL + 'ORDER BY ' + @OrderBy
END

    exec(@ExecSQL)
    --print @ExecSQL
    
    SET @FromIndex=CHARINDEX(' FROM ',UPPER(@QuerySQL))
    --回傳資料總筆數
    SET @ExecSQL = 'SELECT @ReturnCount=COUNT(*)' + SUBSTRING(@QuerySQL,@FromIndex,DATALENGTH(@QuerySQL)-@FromIndex+1)
    EXEC sp_executesql @ExecSQL,N'@ReturnCount int OUTPUT',@ReturnCount OUTPUT
    --print @ExecSQL
END




ASP.NET執行SP範例：





DataTable dtData = new DataTable();
SqlParameter ReturnCount = new SqlParameter("@ReturnCount", SqlDbType.Int);
            using (SqlConnection conn = new SqlConnection(Common.getDBConnectionString((uint)type)))
            {
                SqlCommand cmd = new SqlCommand("usp_QueryByPager", conn);


                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;


                    cmd.Parameters.Add(new SqlParameter("@PageIndex", MasterGridView.PagerIndex));
                    cmd.Parameters.Add(new SqlParameter("@PageSize", MasterGridView.PageSize));
                    cmd.Parameters.Add(new SqlParameter("@QuerySQL", QuerySQL));
                    cmd.Parameters.Add(new SqlParameter("@OrderBy", OrderField));
                    cmd.Parameters.Add(new SqlParameter("@Paging", MasterGridView.AllowPaging == true ? "Y" : "N"));
                    ReturnCount.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(ReturnCount);


                    dtData.Load(cmd.ExecuteReader());
                }
                catch (Exception ex)
                {
                    throw;
                }


            }
            //更新資料總筆數
            MasterGridView.PagerDataCount = Convert.ToInt32(ReturnCount.Value);


            //ReBind Grid
            MasterGridView.DataSource = dtData;
            MasterGridView.DataBind();


原文請參考這篇文章吧