分页的存储过程如下，有一麻烦。该如何解决_ASP.NET

分页的存储过程如下，有一麻烦。
分页的存储过程如下：

create procedure GetAllShopListWithPic
(@PageNumber int,
@ShopsPerPage int,
@HowManyShops int output)
as

declare @Shop table
(RowNumber int,
SID int,
Name nvarchar(50),
IsProm bit)

insert into @Shop

select Row_number() over (order by shop.SID),SID,Name,IsProm
from Shop

select @HowManyShops=count(SID) from @Shop

select SID,Name,IsProm
from @Shop
where
RowNumber>(@PageNumber-1)*@ShopsPerPage
and RowNumber<=@PageNumber*@ShopsPerPage
order by SID DESC

如果按SID的正序来排列的话这种分页很好。
但是一旦我设置order by SID DESC 。采取倒序的排列就有问题。

比如我设置了每页显示3个信息。它的排列则是：
第一页是3，2，1
第二页是6，5，4

我想要的是它这样排列：6，5，4，3，2，1（从第一页到第二页）

有没有好的解决方法呢？？？

------解决方案--------------------------------------------------------
很久没来了，没想到还可以来接分啊，哈哈哈，友情顶一下
------解决方案--------------------------------------------------------
jf
------解决方案--------------------------------------------------------
接分.
------解决方案--------------------------------------------------------
送分就接
------解决方案--------------------------------------------------------

探讨
顺便问一下。

学习ajax的话应该如何下手，很模糊的问题，没人回答就算了哈！继续三分。

------解决方案--------------------------------------------------------
发一个row_number()函数分页的小例子.

没事了可以看一下.

SQL code

ALTER PROCEDURE [dbo].[usp_User_SearchByUserName]    @PageSize int,    @CurrentPage int,    @UserName varchar(50),    @SortBy varchar ( 50 ),    @IsAscOrder bit = 1,    @ItemCount int OUTPUTASBEGIN    SET NOCOUNT ON        DECLARE @UpperBand int, @LowerBand int    -- Calculate the @LowerCount and @UpperCount    SET @LowerBand  = (@CurrentPage - 1) * @PageSize    SET @UpperBand  = (@CurrentPage * @PageSize) + 1    SET @ItemCount = (SELECT COUNT(*) FROM [User] WHERE FirstName LIKE '%' + @UserName + '%');    WITH tempPagedUser AS    (        SELECT    UserID,                FirstName,                LastName,                Age,                Memo,                ROW_NUMBER() OVER (ORDER BY                                        CASE WHEN @SortBy='UserID' AND @IsAscOrder = 1 Then UserID END,                                        CASE WHEN @SortBy='UserID' AND @IsAscOrder = 0 Then UserID END DESC,                                        CASE WHEN @SortBy='FirstName' AND @IsAscOrder = 1 Then FirstName END,                                        CASE WHEN @SortBy='FirstName' AND @IsAscOrder = 0 Then FirstName END DESC,                                        CASE WHEN @SortBy='LastName' AND @IsAscOrder = 1 Then LastName END,                                        CASE WHEN @SortBy='LastName' AND @IsAscOrder = 0 Then LastName END DESC,                                        CASE WHEN @SortBy='Age' AND @IsAscOrder = 1 Then Age END,                                        CASE WHEN @SortBy='Age' AND @IsAscOrder = 0 Then Age END DESC,                                        UserID DESC                                                                            ) AS RowNumber         FROM    [User]         WHERE    FirstName LIKE '%' + @UserName + '%'    )    SELECT    [UserID] [User_UserID],            [FirstName] [User_FirstName],            [LastName] [User_LastName],            [Age] [User_Age],            [Memo] [User_Memo]    FROM    tempPagedUser     WHERE    RowNumber > @LowerBand AND RowNumber < @UpperBand        -- Get The Count Of The Rows That They Meet the Criteria    RETURN @ItemCount    SET NOCOUNT OFF    END
------解决方案--------------------------------------------------------