0 Replies - 1621 Views - Last Post: 02 February 2008 - 05:51 AM

#1 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

SQL 2005 stored procedure for record paging

Posted 02 February 2008 - 05:51 AM

Description: Pass the stored procedure your page index (this will come from your code, and the page size (this also will be set in the code) to the stored procedure and it will return records that are between your page index (-1) times the page size and the index times the page size. I use this particular stored procedure for retrieving entries entered into my database log, it can be modified to fit any table (or set of tables) in your SQL 2005 databaseThis is a stored procedure to help with record paging. With the advent of SQL 2005 a new function was added, ROW_NUMBER, that can help with paging records for you database applications. ROW_NUMBER returns a sequential number, starting at 1, for each row returned in a result set.
CREATE PROCEDURE dbo.GetLoEntries(@PageIndex INT, @PageSize INT)
AS
BEGIN 
	WITH Entries AS (
	SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
	AS Row, Date, Description 
	FROM LOG)
	
	SELECT Date, Description
	FROM Entries 
	WHERE Row BETWEEN 
	(@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize
END 


Is This A Good Question/Topic? 0
  • +

Page 1 of 1