I got this code on internet for Stored Proc paging. This will work if my final result is the order of eployee id.
In my case my final result is in the order of emplyee name. What i have to change in below code so i can have paging in SQL plus my order is by name. I dont want to create Temp table. Becuase i think Creating temp table is overhead to sql server
and in that case i'll use Datagrid defualt paging and return all rows always.(My total number of rows will not be more than 300 and in 70% cases they are below 100)
CREATE PROCEDURE [dbo].[usp_PageResults_NAI]
(
@.startRowIndex int,
@.maximumRows int
)
AS
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
GO
i'm using SQL server 2000|||If name is unique, then just change it to:
DECLARE @.first_name varchar(100), @.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_name = name FROM employees ORDER BY name
-- 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 name >= @.first_name
ORDER BY e.name
SET ROWCOUNT 0
If name is not unique, you might get some overlap, but it won't be a critical issue unless you have a lot of overlap. You could use two columns and include the employeeId for uniqueness, if you wanted to avoid overlapping rows.
|||name will not be Unique|||This should work:
DECLARE @.first_name varchar(100), @.employeeId 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_name = name, @.employeeId = employeeId FROM employees ORDER BY name
-- 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 name > @.first_name
or (name = @.first_name
and employeeId > @.employeeId)
ORDER BY e.name, e.employeeId
SET ROWCOUNT 0
Something like that...
