'Conversion failed' error with ORDER BY CASE expression

I have the following Stored Procedure that works great EXCEPT when I set the 'OrderBy' to 'OrderNumber'.

I get the following error:
Conversion failed when converting the nvarchar value 'SK11270' to data type int.

(SK11270 is a value in the OrderNumber column which is nvarchar(50))

If I run the identical query with any other OrderBy column, it works fine.

I'm completely lost and my head hurts. Can anyone see anything obvious that would be causing this problem?

Thanks in advance for any ideas...

Rich

This works:

sp_jobs '1','20','','JobNumber','ASC','97','True','True','True','True','True','True','True','True','True','False','True','False','True','False','0'

This doesn't:

sp_jobs '1','20','','OrderNumber','ASC','97','True','True','True','True','True','True','True','True','True','False','True','False','True','False','0'

```
PROCEDURE [dbo].[sp_Jobs]

@PageNumber int,
@PageSize int,
@FilterExpression varchar(500),
@OrderBy varchar(50),
@OrderDirection varchar(50),
@CustomerID int,
@ShowNotSet bit,
@ShowPlaced bit,
@ShowProofed bit,
@ShowReProofed bit,
@ShowApproved bit,
@ShowOnTime bit,
@ShowLate bit,
@ShowProblem bit,
@ShowCompleted bit,
@ShowDispatched bit,
@ShowUnapproved bit,
@ShowClosed bit,
@ShowReturned bit,
@ShowNoStock bit,
@UserID int

WITH RECOMPILE

AS

BEGIN
WITH Keys
AS (SELECT TOP (@PageNumber * @PageSize) ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @OrderDirection = 'ASC' THEN
CASE
WHEN @OrderBy = 'JobNumber' THEN p1.JobNumber
WHEN @OrderBy = 'OrderNumber' THEN p1.OrderNumber
WHEN @OrderBy = 'CustID' THEN p1.CustID
WHEN @OrderBy = 'Status' THEN p1.MasterJobStatusID
WHEN @OrderBy = 'DateIn' THEN p1.DateIn
WHEN @OrderBy = 'DateDue' THEN p1.DateDue
WHEN @OrderBy = 'DateOut' THEN p1.DateOut
ELSE NULL
END
END ASC
, CAS

Your CASE expression can only have one datatype. But yours is mixing datatypes and the highest one is being used (as per datatype precedence). So nvarchar is changed to int based on these rules.

You need a case per datatype or per column

Example:

CASE WHEN @OrderDirection = 'DESC' THEN
                 CASE 
                  WHEN @OrderBy = 'JobNumber' THEN J.JobNumber
                  WHEN @OrderBy = 'CustID' THEN J.CustID
                  WHEN @OrderBy = 'DateIn' THEN J.DateIn
                  WHEN @OrderBy = 'DateDue' THEN J.DateDue
                  WHEN @OrderBy = 'DateOut' THEN J.DateOut
                  WHEN @OrderBy = 'Status' THEN J.MasterJobStatusID 
                  ELSE NULL
                END
            END DESC,
CASE WHEN @OrderDirection = 'DESC' THEN
                 CASE 
                  WHEN @OrderBy = 'OrderNumber' THEN J.OrderNumber
                  ELSE NULL
                END
            END DESC

StackExchange Database Administrators Q#4162, answer score: 17