You can remove the big CASE clause within the CTE and build the query dynamically. Something like below...

Create procedure dbo.Product_SEL_PD-- 'code','desc',10,1

@SortColumn nvarchar(255),

@SortDirection nvarchar(4) = 'asc' ,

@rowsPerPage int = 10,

@pageNum int = 100000,

@SQL nvarchar(2000)

/*

some checks here to prevent SQL Injection from nvarchar columns

*/

set @SQL = '

;WITH PaginatedProduct AS (

SELECT Top(@rowsPerPage * @pageNum)

ROW_NUMBER() OVER (ORDER BY ' + @SortColumn + ' ' + @SortDirection + ') AS RowNumber,

id,code,description,price,lot,uom

FROM dbo.Product

)

select * from PaginatedProduct WHERE RowNumber > ((@pageNum - 1) * @rowsPerPage) ORDER BY RowNumber

'

EXEC sp_executesql @SQL, N'@rowsPerPage int, @pageNum int', @RowsPerPage = @RowsPerPage, @pageNum = @pageNum

GO