Store Procedure to be Used in Custom Pagging

Lakhangarg
Posted by Lakhangarg under Sql Server category on | Views : 1454
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
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
http://lakhangarg.blogspot.com/

Comments or Responses

Login to post response