SQL Server 2005 introduced a beautiful new world of 'ranking functions', one of which we can use to easily perform pagination directly in SQL , Which will improve performance tremendously . Only page number and page size is to be passed for pagination to Stored procedure for implementing pagination . This is easy and fast . The code snippet below is showing how to implement the pagination with sql server native method.
CREATE PROCEDURE dbo.StoredProcedureName1
(
@pi_PageNumber INT , -- Required page number
@pi_PageSize INT , -- Total number of records ro be displayed o
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @StartRow INT; --This will be rownumber of first row of required page number
DECLARE @EndRow INT; -- This will be last row of page
--This line will calculate first row of the required page
SELECT @StartRow = (@pi_PageNumber * @pi_PageSize) - @pi_PageSize + 1
--This line will calculate Last row of the required page
SELECT @EndRow = @StartRow + @pi_PageSize - 1
/*
Get the "row number" from a result set. This is the basis of pagination because you need to know what row you are on to begin demanding only a subset from SQL . Move the ORDER BY clause inside the OVER clause. Basically what this is doing is telling SQL how we want the ROW_NUMBER() function to number each record.
*/
SELECT
ColumnName1, ColumnName2, ColumnName3, ColumnName4
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ColumnName1, ColumnName2) AS RowNumber,ColumnName1, ColumnName2, ColumnName3, ColumnName4
from
TableName1
) AS TempTable
WHERE RowNumber between @StartRow and @EndRow
/* The first query is aliased and called it "TempTable". This basically treats that inner query as a VIEW so that I could pull out that first column by it's name, RowNumber
*/
RETURN 0;
END
