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.)
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
Thanks & Regards
Lakhan Pal Garg
Free Code Snippets
http://lakhangarg.blogspot.com
|
| Author: Alwyn 11 Nov 2009 | Member Level: Gold Points : 2 |
This will not work accurately it may go wrong 90%.
Suppose, i have 4 records and doing paging by 2 rows per page
1 - q
2 - w
3 - e
4 - r
when i run the query for the first time, it will show
1 - q
2 - w
in the mean while, some user has removed the 2nd record
so the remaining values will be like;
1 - q
3 - e
4 - r
now when we run the query for the second time, it will display only
4 - r
but it should show
3 - e
4 - r
henc we cannot use this code.
|