Advanced Search

More Efficient Method for Paging

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. CREATE  PROCEDURE [dbo].[usp_PageResults_NAI] 
  2. (
  3.     @startRowIndex INT,
  4.     @maximumRows INT
  5. )
  6. AS
  7.  
  8. DECLARE @first_id INT, @startRow INT
  9.  
  10. -- A check can be added to make sure @startRowIndex isn't > count(1)
  11. -- from employees before doing any actual work unless it is guaranteed
  12. -- the caller won't do that
  13.  
  14. -- Get the first employeeID for our page of records
  15. SET ROWCOUNT @startRowIndex
  16. SELECT @first_id = employeeID FROM employees ORDER BY employeeid
  17.  
  18. -- Now, set the row count to MaximumRows and get
  19. -- all records >= @first_id
  20. SET ROWCOUNT @maximumRows
  21.  
  22. SELECT e.*, d.name AS DepartmentName 
  23. FROM employees e
  24.    INNER JOIN Departments D ON
  25.        e.DepartmentID = d.DepartmentID
  26. WHERE employeeid >= @first_id
  27. ORDER BY e.EmployeeID
  28.  
  29. SET ROWCOUNT 0
  30.  
  31. GO

URL: http://www.4guysfromrolla.com/webtech/042606-1.shtml

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.