I found this example for paging records with a stored procedure.
This problem is I need a little more explanation. I know how to call a sp but don't understand the paging part. How do you know what value to send as startRowIndex? What about retuning the total count of recoreds found? What would the paging code look like in classic?
CREATE PROCEDURE [dbo].[usp_PageResults_NAI
DECLARE @first_id int, @startRow int
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that
-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid
-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows
SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID
SET ROWCOUNT 0
Hope I'm not asking to much...