Concatenating Parameter Values && Text; Nested CASE Statements

Hello.  I'm trying to reduce some code in my stored procedure and I'm running into lots of errors.  I'm somewhat of a novice with SQL and stored procedures so any help would be beneficial.

I have a SP that gets a page of user data and is also called when sorting by one of the columns (this data is placed in a repeater, btw).  I quickly learned that I wasn't able to pass in string parameters the way I had hoped in order to handle the ORDER BY and direction (ASC/DESC) so I'm trying to work around this.

So far I've tried the following with many errors.WITH Users AS (
SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderBy='FirstName' AND @Direction='DESC' THEN (FirstName + ' DESC')
WHEN @OrderBy='FirstName' THEN FirstName
WHEN @OrderBy='LastName' AND @Direction='DESC' THEN (LastName + ' DESC')
WHEN @OrderBy='LastName' THEN LastName
END
) AS Row,
UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName
FROM
vRF_Users
)

SELECT UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName
FROM Users
WHERE Row BETWEEN @StartRowIndex AND @EndRowIndex

 

I've tried a combination of similar things with parenthesises, without, doing "THEN FirstName DESC" without concatenating anything, etc.

I also tried: DECLARE @OrderByDirection varchar(32)
DECLARE @DESC varchar(4)
SET @DESC = ' DESC'

IF @Direction = 'DESC'
BEGIN
SET @OrderByDirection = (@OrderBy + @DESC)
END

And then writing my case statemet like this:ORDER BY CASE WHEN @Direction='DESC' THEN @OrderByDirection
ELSE @OrderBy
ENDObviously this didn't work either.  Is there any way to gracefully accomplish this or do I just have to use a bunch of if/else statements and lots of redundant code to evaluate all my @OrderBy and @Direction parameters???

 

Thanks in advance,

Jen