Posted By

samwa on 05/07/11


Tagged

sql paging


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

samwa


More Efficient Method for Paging


 / Published in: SQL
 

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

  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

Report this snippet  

You need to login to post a comment.