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


Top 5 Contributors of the Month
Steve Jackman
Imran Ghani
Santhakumar Munuswamy

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.)

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

     

Further Readings:

Responses

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