Store Procedure to be Used in Custom paging. 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.)
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
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 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.
Thanks & Regards
Lakhan Pal Garg
Free Code Snippets