ALTER PROCEDURE [dbo].[usp_User_SearchByUserName]
    @PageSize int,
    @CurrentPage int,
    @UserName varchar(50),
    @SortBy varchar ( 50 ),
    @IsAscOrder bit = 1,
    @ItemCount int OUTPUT
AS

BEGIN
    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

------解决方案--------------------