.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Home >> Code Snippets >> ADO.NET >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Store Procedure to be Used in Custom paging

Posted By:Lakhan Pal Garg       Posted Date: September 25, 2009    Points: 10    Category: ADO.NET    URL: http://www.dotnetspark.com  

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

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


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend