SQL Conversion failed Error

Question

WhenI call this stored procedure:

ALTER PROCEDURE [dbo].[GetSorted]   
(
   @OrderByColumn   nvarchar(256)

)
AS
SET NOCOUNT ON   

   SELECT itDocs.AddedDate, itDocs.AddedBy  FROM itDocs      
    ORDER BY 
             CASE WHEN @OrderByColumn='AddedDate' THEN itDocs.AddedDate
               WHEN @OrderByColumn='AddedBy' THEN itDocs.AddedBy 

        END  ASC

I get error:

Conversion failed when converting date and/or time from character string

.

This is how I call SP:

DECLARE @return_value int

EXEC    @return_value = [dbo].[GetSorted]

     @OrderByColumn = 'AddedBy'


SELECT  'Return Value' = @return_value

GO

UPDATE: If I use multi-case approach as suggested in fist 2 answers, I get error when trying to added another case for orientation:

  ORDER BY  
        CASE WHEN @OrderDirection=0 THEN            
           CASE WHEN @OrderByColumn='AddedDate' THEN itDocs.AddedDate END ASC,
           CASE WHEN @OrderByColumn='AddedBy' THEN   itDocs.AddedBy END ASC    
        end       
       CASE WHEN @OrderDirection=1 THEN

Answer 1

You are trying to convert datatypes and datetime has a higher precedence. Basically "AddedBy = 'bob'" can not be converted...

SELECT
    itDocs.AddedDate, itDocs.AddedBy
FROM
    itDocs      
ORDER BY
    CASE OrderByColumn WHEN 'AddedDate' THEN itDocs.AddedDate ELSE '19000101' END,
    CASE OrderByColumn WHEN 'AddedBy' THEN itDocs.AddedBy ELSE '' END

Answer 2

Here is a good article explaining the error you are receiving:

Using CASE in ORDER BY clause

You can change your stored procedure to the following to allow the CASE in the ORDER BY clause to work (now edited to include order direction as a parameter):

ALTER PROCEDURE [dbo].[GetSorted]   
(
    @OrderByColumn   nvarchar(256),
    @OrderDirection  int
)
AS
SET NOCOUNT ON             
   SELECT itDocs.AddedDate, itDocs.AddedBy FROM itDocs          
      ORDER BY              
         CASE WHEN @OrderByColumn='AddedDate' AND @OrderDirection=0 THEN itDocs.AddedDate END ASC,
         CASE WHEN @OrderByColumn='AddedBy' AND @OrderDirection=0 THEN itDocs.AddedBy END ASC,         
         CASE WHEN @OrderByColumn='AddedDate' AND @OrderDirection=1 THEN itDocs.AddedDate END DESC,
         CASE WHEN @OrderByColumn='AddedBy' AND @OrderDirection=1 THEN itDocs.AddedBy END DESC