SQL paged search results

I'm trying to figure out a way to simplify implementing paged searching in sql server.

My restrictions are:

• has to be in a proc.

• can't use dynamic sql.

Here's my sample query:

DECLARE @TenantId int
DECLARE @MemberStatusId int
DECLARE @SearchTerm varchar(50)
DECLARE @ColumnName varchar(30)
DECLARE @PageNum int
DECLARE @RecCount int

;
WITH RowPages AS (
    SELECT TOP( @PageNum * @RecCount )
            RowNumber = ROW_NUMBER() OVER( ORDER BY 
                CASE
                    WHEN @ColumnName = 'Name' THEN P.LastName
                    WHEN @ColumnName = 'PhoneNumber' THEN M.PhoneNumber
                    WHEN @ColumnName = '' THEN P.LastName
                END ASC,
                CASE 
                    WHEN @ColumnName = 'DateAdded' THEN P.DateAdded
                END DESC
                ),
            TotalCount = COUNT(M.MemberId) OVER ( PARTITION BY NULL),
            M.MemberId, M.ExternalId, M.PhoneNumber, P.FirstName, P.LastName, P.EmailAddress
        FROM membership.Members M (nolock)
            INNER JOIN core.Persons P (nolock) on (P.PersonId = M.MemberId)
        WHERE (M.TenantId = @TenantId)
          AND ((@MemberStatusId = 0) or (M.MemberStatusId = @MemberStatusId))
          AND (
                (@SearchTerm = '""')
                OR (CONTAINS( (P.FirstName, P.LastName, P.EmailAddress), @SearchTerm))
            )
)
SELECT TotalCount, MemberId, ExternalId, PhoneNumber,
        FirstName, LastName, EmailAddress
    FROM RowPages
    WHERE RowNumber > ((@PageNum - 1) * @RecCount)
    ORDER BY RowNumber

What this does is perform a paged search of a couple tables. You can pass in the page number and # records per page as well as the column to sort by and it has a full text search component. This is one of the simpler queries of this style we have.

What I'm trying to figure out is how to optimize this. The biggest costs in the execution plan are in regards to the full text search. There is a table value functi

It looks pretty good to me. With a cost of 100% on the FullTextMatch, I'd consider running this as a standard statement and hard code the variables just to see what the execution plan says.
I do have one question though. Your case statement for @ColumnName = 'DateAdded'... doesn't this throw an error when @ColumnName <> 'DateAdded' as you end up with something like:

order by P.LastName asc, desc

StackExchange Code Review Q#1258, answer score: 3