Error in Order by clause when using case statement in SQL Server

Question

I have a stored procedure defined as below. When I execute it, I get an error and couldn't come up with a solution.

ALTER PROCEDURE [dbo].[GetShippmentList] 
    @shipmentNumber     VARCHAR(20),  
    @requestType VARCHAR(1),
    @shipmentNames VARCHAR(100),
    @assigneeDateFrom DATETIME,
    @assignedDateTo DATETIME,
    @completedDateFrom DATETIME,
    @completedDateto DATETIME,
    @status VARCHAR(20), 
    @userId VARCHAR(20),
    @pageNo int,
    @pageSize int,
    @sortField VARCHAR(20),
    @sortOrder VARCHAR(4)
AS 
BEGIN
    SET NOCOUNT ON

    IF OBJECT_ID('tempdb..##ApTemp') IS  NULL
    BEGIN
        CREATE TABLE ##ApTemp(INST_ID VARCHAR(32), 
                              STATUS NVARCHAR(16), 
                              NAME NVARCHAR(64), 
                              ASSIGNED_DATE DATETIME NULL, 
                              COMPLETED_DATE DATETIME NULL, 
                              USER_ID NVARCHAR(64) )

        INSERT INTO ##ApTemp(INST_ID, STATUS, NAME, ASSIGNED_DATE, COMPLETED_DATE, USER_ID)
            SELECT 
                w.INST_ID, w.STATUS, w.NAME, w.ASSIGNED_DATE,
                w.COMPLETED_DATE, w.USER_ID 
            FROM 
                WestShipment.Shipmentt_Prod.dbo.WShipments w 
            WHERE 
                w.NAME IN ('T1', 'T2', 'T5', 'T51', 'T3', 'T31') 
                AND w.STATUS NOT IN ('Removed', 'Cancelled') 
                AND w.APP IN ('East', 'West')
    END
AS (
    SELECT  w.INST_ID, w.NAME, w.ASSIGNED_DATE AS AssignedDate, w.COMPLETED_DATE  AS CompletedDate,w.STATUS AS WfStatus, w.USER_ID AS User_Id,
            STUFF(
                    (SELECT '','' + t.USER_ID 
                    FROM ##ApTemp  t
                    WHERE t.INST_ID = w.Inst_ID AND t.NAME = w.Name
                    FOR XML PATH ('')), 1, 1, '') AS Assignees
    FROM ##ApTemp  AS w  
    WHERE (w.NAME IN( @shipmentNames) OR LEN(@shipmentNames) > 4)
    AND ( w.STATUS IN (@status) OR LEN(@status) > 4)
    AND ((w.ASSIGNED_DATE  BETWEEN @assigneeDateFrom AND @assignedDateTo) OR LEN(@assigneeDateFrom) >4 OR LEN(@assignedDateTo) > 4)
    AND ((w.COMPLETED_DATE  BETWEEN @completedDateFrom AND @completedDateto) OR LEN(@completedDateFrom) >4 OR LEN(@completedDateto) >4)
    AND (w.User_ID LIKE @userId OR LEN(@userId) > 4 )
    GROUP BY w.NST_ID, w.NAME , w.ASSIGNED_DATE, w.COMPLETED_DATE,w.STATUS, User_Id
)
INSERT INTO #tempTable(INST_ID, NAME, AssignedDate, CompletedDate, WfStatus, Assignees, ShippmentNumber , ShipmentName , RequestType )
SELECT w.INST_ID, w.NAME, w.AssignedDate, w.CompletedDate, w.WfStatus, w.Assignees,  m.DocumentNumber, m.ShipmentName, m.RequestType
FROM dbo.DncMain m INNER JOIN workflows w ON m.InstanceId = w.INST_ID 
WHERE (m.ShipmentNumber = @shipmentNumber OR LEN(@shipmentNumber) > 4)
      AND(m.RequestType = @requestType OR @requestType NOT LIKE '0')
IF @sortOrder = 'DESC'
    begin
    SELECT  INST_ID, NAME, AssignedDate, CompletedDate, WfStatus, Assignees, ShipmentNumber, ShipmentName FROM #tempTable
    ORDER BY CASE @sortField
             WHEN 'ShipmentNumber' THEN ShipmentNumber
             WHEN 'TaskName' THEN NAME  
             WHEN 'ShipmentName' THEN ShipmentName
             WHEN 'AssignedDate' THEN AssignedDate
             WHEN 'CompletedDate' THEN CompletedDate
             WHEN 'Assignees' THEN Assignees
             WHEN 'Status' THEN WfStatus
             END DESC OFFSET (@pageNo) ROWS FETCH NEXT(@pageSize) ROW ONLY
    end 
ELSE
begin
    SELECT TOP(@pageNo) INST_ID, NAME, AssignedDate, CompletedDate, WfStatus, Assignees, ShipmentNumber, ShipmentName FROM #tempTable
    ORDER BY CASE @sortField
             WHEN 'TaskName' THEN NAME  
             WHEN 'ShipmentName' THEN ShipmentName
             WHEN 'ShipmentNumber' THEN ShipmentNumber
             WHEN 'AssignedDate' THEN AssignedDate
             WHEN 'CompletedDate' THEN CompletedDate
             WHEN 'Assignees' THEN Assignees
             WHEN 'Status' THEN WfStatus
             END ASC OFFSET (@pageNo) ROWS  FETCH NEXT(@pageSize) ROW ONLY
    end
SELECT COUNT(1) AS TotalRows FROM #tempTable
END
GO

The error I got is :

Msg 8115, Level 16, State 2, Procedure GetShipmentList, Line 60
Arithmetic overflow error converting expression to data type datetime.

The problem looks like in the order by clause. If I remove the case statement from the ORDER BY clause, it works fine.

Answer 1

Another approach is to use dynamic SQL to generate different SQL for the different choices, allowing the optimizer to choose the best plan for each.

DECLARE @ComputedSortField SYSNAME = CASE @sortField
        WHEN 'ShipmentNumber' THEN N'ShipmentNumber'
        WHEN 'TaskName' THEN N'NAME'  
        WHEN 'ShipmentName' THEN N'ShipmentName'
        WHEN 'AssignedDate' THEN N'AssignedDate'
        WHEN 'CompletedDate' THEN N'CompletedDate'
        WHEN 'Assignees' THEN N'Assignees'
        WHEN 'Status' THEN N'WfStatus'
    END
DECLARE @TopClause NVARCHAR(13) = CASE @SortOrder 
        WHEN 'DESC' THEN N'' 
        ELSE N'TOP(@pageNo)'
    END
DECLARE @ComputedSortOrder NVARCHAR(4) = CASE @SortOrder
         WHEN 'DESC' THEN N'DESC'
         ELSE N'ASC'
    END
DECLARE @Sql NVARCHAR(MAX) = N'SELECT ' + @TopClause + N' INST_ID, NAME, AssignedDate, 
         CompletedDate, WfStatus, Assignees, ShipmentNumber, ShipmentName 
    FROM #tempTable
    ORDER BY ' + @ComputedSortField + N' ' + @ComputedSortOrder + N'
    OFFSET (@pageNo) ROWS FETCH NEXT(@pageSize) ROW ONLY'

PRINT @Sql
EXECUTE sp_executesql @Statement = @Sql
    , @Params = N'@pageNo int, @pageSize int'
    , @PageNo = @PageNo
    , @PageSize = @PageSize

Note that all the pieces being concatenated together are defined within the code. There is nothing from a parameter that is being concatenated into the string to avoid SQL injection attacks.

If @SortField does not match one of the defined sorts @ComputedSortField will be null causing @Sql to be null. You may want to define a default @ComputedSortField or make an @OrderByClause that can be set to '', removing sorting, when a proper @SortField value is passed.

Answer 2

This is an order by:

ORDER BY CASE @sortField
         WHEN 'TaskName' THEN NAME  
         WHEN 'ShipmentName' THEN ShipmentName
         WHEN 'ShipmentNumber' THEN ShipmentNumber
         WHEN 'AssignedDate' THEN AssignedDate
         WHEN 'CompletedDate' THEN CompletedDate
         WHEN 'Assignees' THEN Assignees
         WHEN 'Status' THEN WfStatus
         END ASC 

This is a single expression in SQL. It returns one specific type -- regardless of which WHEN clause is being executed. That causes a problem.

So, another way of writing the logic is to split this to different case statements:

ORDER BY (CASE @sortField WHEN 'TaskName' THEN NAME END) ASC,  
         (CASE @sortField WHEN 'ShipmentName' THEN ShipmentName END) ASC,
         (CASE @sortField WHEN 'ShipmentNumber' THEN ShipmentNumber END) ASC,
         (CASE @sortField WHEN 'AssignedDate' THEN AssignedDate END) ASC,
         (CASE @sortField WHEN 'CompletedDate' THEN CompletedDate END) ASC,
         (CASE @sortField WHEN 'Assignees' THEN Assignees END) ASC,
         (CASE @sortField WHEN 'Status' THEN WfStatus END) ASC

This puts each column as a separate key, so you cannot get conflicts.

Answer 3

You are trying to convert a number to a datetime, which you cannot do. First, you must convert it to a string (varchar).

For example:

declare @IntegerDate int = 20151223

select cast(@IntegerDate as datetime) //this will throw an error

select cast(cast(@IntegerDate as varchar) as datetime) //This will work