SET @OrderByColumn = 'AddedDate'
SET @OrderDirection=1;
.
.
.
 ORDER BY
      CASE WHEN @OrderByColumn='AddedDate' THEN CONVERT(varchar(50),AddedDate)
           WHEN @OrderByColumn='Visible' THEN CONVERT(varchar(2), Visible)
           WHEN @OrderByColumn='AddedBy' THEN AddedBy
           WHEN @OrderByColumn='Title' THEN Title
      END

ORDER BY
      CASE WHEN @OrderDirection=0 THEN 1
      ELSE
           CASE WHEN @OrderByColumn='AddedDate' THEN CONVERT(varchar(50),AddedDate)
                WHEN @OrderByColumn='Visible' THEN CONVERT(varchar(2), Visible)
                WHEN @OrderByColumn='AddedBy' THEN AddedBy
                WHEN @OrderByColumn='Title' THEN Title
           END
      END ASC,
      CASE WHEN @OrderDirection=1 THEN 1
      ELSE
           CASE WHEN @OrderByColumn='AddedDate' THEN CONVERT(varchar(50),AddedDate)
                WHEN @OrderByColumn='Visible' THEN CONVERT(varchar(2), Visible)
                WHEN @OrderByColumn='AddedBy' THEN AddedBy
                WHEN @OrderByColumn='Title' THEN Title
           END
      END DESC

CREATE PROCEDURE GetProducts
(
    @OrderBy      VARCHAR(50),
    @Input2       VARCHAR(30)
)
AS
BEGIN
    SET NOCOUNT ON

    SELECT Id, ProductName, Description, Price, Quantity
    FROM Products
    WHERE ProductName LIKE @Input2
    ORDER BY
        CASE
            WHEN @OrderBy = 'ProductNameAsc' THEN ProductName
        END ASC,
        CASE
            WHEN @OrderBy = 'ProductNameDesc' THEN ProductName
        END DESC

END

DECLARE @multiplier int;

SELECT @multiplier = CASE @Direction WHEN 1 THEN -1 ELSE 1 END;

SELECT
     Columns you actually want
FROM
    (
    SELECT
         Columns you actually want,
         ROW_NUMBER() OVER (ORDER BY AddedDate) AS AddedDateSort,
         ROW_NUMBER() OVER (ORDER BY Visible) AS VisibleSort,
         ROW_NUMBER() OVER (ORDER BY AddedBy) AS AddedBySort,
         ROW_NUMBER() OVER (ORDER BY Title) AS TitleSort
    FROM
         myTable
    WHERE
         MyFilters...
    ) foo
ORDER BY
     CASE @OrderByColumn
        WHEN 'AddedDate' THEN AddedDateSort
        WHEN 'Visible' THEN VisibleSort
        WHEN 'AddedBy' THEN AddedBySort
        WHEN 'Title' THEN TitleSort
     END * @multiplier;