Dynamic Order Clause

I am trying to set up a stored procedure based on what the user selects, will sort the result set by the following:
Low to high
High to low
Name

And this is what I have


DECLARE @bigCategoryId INT
DECLARE @intPageLen INT
DECLARE @intPageIndex INT
DECLARE @strSortOrder VARCHAR(10)
DECLARE @intStartRowIndex INT
   
    SET @strSortOrder = 'nme'
    SET @bigCategoryId = 50
    SET @intpagelen = 16
    SET @intPageIndex = 1
    SET @intStartRowIndex = ((@intPageIndex-1) * @intPageLen) + 1;
      
      --Pull back the product listing
    WITH ProductListing AS (
        SELECT ROW_NUMBER() OVER (ORDER BY sic.id ASC) AS row, sic.id,
                   rtrim(sic.invtid) AS invtid,prd.descr,
            dbo.func_priceproduct_getprice_itemonly(prd.invtid,1) AS price
            FROM siteitemcategory sic WITH (NOLOCK)
            INNER JOIN products prd ON prd.invtid = sic.invtid
            WHERE sitecategories_id
            IN (SELECT id FROM siteCategories WHERE menu_parent = @bigCategoryId OR id = @bigCategoryId)
    )
   
    SELECT row,id,invtid,descr, price
            FROM productlisting
            WHERE Row
                        BETWEEN @intStartRowIndex AND @intStartRowIndex+@intPageLen-1
ORDER BY CASE WHEN @strSortOrder = 'low' THEN price
              WHEN @strSortOrder = 'high' THEN price
              WHEN @strSortOrder = 'nme' THEN descr
              ELSE descr
              END


However I am getting the following message all the time
Msg 235, Level 16, State 0, Line 15
Cannot convert a char value to money. The char value has incorrect syntax.

I know right now the desc is at the end of the case statement, but I was hoping to make this part of the case statement result.

I really am trying to avoid writing dynamic sql for this or putting a if clause in place based on the value of the sort type. Any help would be appreciated. Thanks.
Start Free Trial