排序整个GridView与自定义页面控制

在GridView中，我在创建自定义分页控件时关闭了分页。我有一个SQL存储过程，我传入页码作为参数返回。我现在的问题是，排序不再工作，除了当前记录返回1000。当我打开排序时，当前的1000条记录被排序，而不是整个记录集。我确信这是因为我的存储过程也只返回1000条记录。我能想到的唯一方法是将所有列名作为case语句传递，并根据它们作为附加参数进行排序。一定有比这更好的办法。任何想法吗?

这是我目前正在做的，没有排序也能很好地工作。@StartRow和@EndRow总是基于传递的页码，其中@StartRow等于页码* 1000(每页返回1000行)。

@StartRow INT,
@EndRow INT
AS
SELECT * FROM
    (
    SELECT ROW_NUMBER() OVER (ORDER BY EmployeeID) AS Row,
        HR.EmployeeID, HR.FirstName, HR.LastName,
        ISNULL(Locations.City,'') + ', ' + ISNULL(Locations.State,'') as LocationName
    FROM HR
        INNER JOIN Locations ON HR.LocationID = Locations.LocationID
    ) AS X
GROUP BY X.Row, X.EmployeeID, X.FirstName, X.LastName
HAVING X.Row BETWEEN @StartRow AND @EndRow
ORDER BY X.Row

这是我能想到的在使用自定义分页控件时完成排序的唯一方法。虽然我认为有一个更好的方法，只是没有想到。

@StartRow INT,
@EndRow INT,
@Sort nvarchar(25)
AS
CREATE TABLE #Temp1
(
            EmployeeID nvarchar(25),
            FirstName nvarchar(25),
            LastName nvarchar(25),
            LocationName nvarchar(25)
)
INSERT INTO #Temp1
SELECT * FROM
    (
    SELECT 
        HR.EmployeeID, HR.FirstName, HR.LastName,
        ISNULL(Locations.City,'') + ', ' + ISNULL(Locations.State,'') as LocationName
    FROM HR
        INNER JOIN Locations ON HR.LocationID = Locations.LocationID
    ) AS X
GROUP BY X.EmployeeID, X.FirstName, X.LastName, X.LocationName
SELECT * FROM
    (
    SELECT ROW_NUMBER() OVER (ORDER BY 
                                CASE 
                                    WHEN @Sort = 'EmployeeID' THEN #Temp1.EmployeeID 
                                    WHEN @Sort = 'FirstName' THEN #Temp1.FirstName
                                    WHEN @Sort = 'LastName' THEN #Temp1.LastName
                                    WHEN @Sort = 'LocationName' THEN #Temp1.LocationName
                                    ELSE #Temp1.EmployeeID
                                END 
    ) AS Row, *
    FROM #Temp1 
    ) AS X
WHERE X.Row BETWEEN @StartRow AND @EndRow
drop table #Temp1

SET @SqlString = N'SELECT... ORDER BY ' + @Sort
EXECUTE sp_executesql @SQLString