Questions on use of SQL Server 2005 functionality in Gridview Paging

I have a webpage that displays 4000 or more records in a GridView control powered by a SqlDataSource. It's very slow. I'm reading the following article on custom paging: http://aspnet.4guysfromrolla.com/articles/031506-1.aspx. This article uses an ObjectDataSource, and some functionality new to Sql Server 2005 to implement custom paging.

There is a stored procedure called GetEmployeesSubestByDepartmentIDSorted that looks like this:

ALTER PROCEDURE dbo.GetEmployeesSubsetByDepartmentIDSorted
(
@.DepartmentID int,
@.sortExpression nvarchar(50),
@.startRowIndex int,
@.maximumRows int
)
AS
IF @.DepartmentID IS NULL
-- If @.DepartmentID is null, then we want to get all employees
EXEC dbo.GetEmployeesSubsetSorted @.sortExpression, @.startRowIndex, @.maximumRows
ELSE
BEGIN
-- Otherwise we want to get just those employees in the specified department
IF LEN(@.sortExpression) = 0
SET @.sortExpression = 'EmployeeID'

-- Since @.startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), increment
SET @.startRowIndex = @.startRowIndex + 1

-- Issue query
DECLARE @.sql nvarchar(4000)
SET @.sql = 'SELECT EmployeeID, LastName, FirstName, DepartmentID, Salary,
HireDate, DepartmentName
FROM
(SELECT EmployeeID, LastName, FirstName, e.DepartmentID, Salary,
HireDate, d.Name as DepartmentName,
ROW_NUMBER() OVER(ORDER BY ' + @.sortExpression + ') as RowNum
FROM Employees e
INNER JOIN Departments d ON
e.DepartmentID = d.DepartmentID
WHERE e.DepartmentID = ' + CONVERT(nvarchar(10), @.DepartmentID) + '
) as EmpInfo
WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @.startRowIndex) +
' AND (' + CONVERT(nvarchar(10), @.startRowIndex) + ' + '
+ CONVERT(nvarchar(10), @.maximumRows) + ') - 1'

-- Execute the SQL query
EXEC sp_executesql @.sql
END

The part that's bold is the part I don't understand. Can someone shed some light on this for me? What is this doing and why?

Diane

"ROW_NUMBER() OVER(ORDER BY ' + @.sortExpression + ') as RowNum" part is generating the row number by sorting the data on sortExpression and fetching the data from employee and department. It also doing the paging on the backend side based on the row#

Check this for easy understanding:http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx

|||

Thank you, I have a better understanding of this, but I'm still not sure how to use it. I want to return all the fields in the table. Will this work?

SET @.sql = 'SELECT <full fields list>

FROM (SELECT <full field list>, ROW_NUMBER() OVER(ORDER BY ' + @.sortExpression + ') as RowNum FROM <table> WHERE <condition>)

as tblinfo

WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @.startRowIndex) + ' AND (' + CONVERT(nvarchar(10), @.startRowIndex) + ' + ' + CONVERT(nvarchar(10), @.maximumRows) + ') - 1'

Is this creating a table in a table? The article didn't explain why there's a select statement inside the from?

Diane

|||

This is the subquery. There are # of aricle on the same subject.

http://www.aspfree.com/c/a/MS-SQL-Server/Subqueries-and-Query-Expressions/

|||

Thank you. I got the query and sub query to work. Now my problem is that it will return the expected data when I preview it, but the gridview shows no data.

Diane