Store Procedure to be Used in Custom Pagging

With the help of the below set of query we can get the number of record that we want to show to user.

For this we need to pass two parameters. first is the @RecordsToPick (Number of Records that you want to picjk for the page) and the second is @PageNumber (Page number for which you want to get the records from Database.)

USE DBName
GO
DECLARE @RecordsToPick smallint, @PageNumber smallint
SET @RecordsToPick = 10
SET @PageNumber = 2

DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = ((@PageNumber-1) * @RecordsToPick)
SET @EndRow = @StartRow + @RecordsToPick

SELECT * FROM ( SELECT UserName,UserID,City,State,Country ROW_NUMBER()OVER(ORDER BY UserID) AS RowNumber
FROM as_TblMembers) As AliasName WHERE RowNumber > @StartRow AND RowNumber <= @EndRow GO

In the above store procedure first we will get the @StartRow and @EndRow to get the number of first record and last record respectively.

ROW_NUMBER()OVER(ORDER BY UserID) AS RowNumber

ROW_NUMBER() will assign a unique number to each query order by UserID. with the help of it is easy for us to get the required result.