sql order by variable with different data type

Question

I have following query

DECLARE @OrderBy INT
SET @OrderBy = 1
DECLARE @PageSize INT
SET @PageSize = 10
DECLARE @PageIndex INT
SET @PageIndex = 1


SELECT exp.ExportId
    ,exp.ExportDate
    ,exp.UniqueExportId
    ,exp.ExporterOfRecord
    ,exp.PartNo
    ,exp.Description
    ,exp.AvailQty AS Quantity
    ,exp.UnitOfMeasuremnt
    ,exp.Destination
    --,exp.ScheduledB,
    ,tp.HTSUS AS ScheduledB
FROM tblCASIDD_Export exp
    LEFT JOIN tblCASIDD_Part tp
    ON tp.PartNo = exp.PartNo


ORDER BY (
        CASE @OrderBy
            WHEN 1
                THEN exp.PartNo
            WHEN 2
                THEN exp.ExportDate
            END
        ) OFFSET @PageSize * (@PageIndex - 1) ROWS

FETCH NEXT @PageSize ROWS ONLY

when i give orderby 1 (varchar) it throws error

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

Its throwing error with data type other than date type

while with date type its working

any suggestion what i am doing wrong

Thanks

StuartLC · Accepted Answer · 2014-01-13T11:40:58.507

3

The types of all options in the ORDER BY CASE statement need to match, so you'll need to cast to something common e.g.

CASE @OrderBy
        WHEN 1
            THEN CAST(exp.PartNo AS NVARCHAR(20))
        WHEN 2
            THEN CAST(exp.ExportDate AS NVARCHAR(20))
        END

(The optimal type to cast to will depend on the native types of PartNo and ExportDate)

SqlFiddle here

edited Jan 13 '14 at 11:40

answered Jan 13 '14 at 11:35

StuartLC

104,537
17
209
285

With cast i have done, but why its throwing error in different datat type – Md. Parvez Alam Jan 13 '14 at 11:43
Can you please give the exact types of `PartNo` and `ExportDate` ? – StuartLC Jan 13 '14 at 11:51
1

@Md.ParvezAlam - because `CASE` expressions are a general form of expression - it's an *unusual* case here that all rows will return values from the *same* `THEN` clause. As such, the code has to be generated to expect a mix of values from any of the `THEN` clauses (or the `ELSE` clause). And so SQL has to decide what the data type is for the entire `CASE` expression - it takes all of the possible data types from each clause, and then uses the precedence rules to determine a single data type that all the clauses values will be converted to. In your case, it picked `datetime`. – Damien_The_Unbeliever Jan 13 '14 at 11:58

score 1 · Answer 2 · edited May 23 '17 at 11:49

As Stuart says, all possible values of a CASE expression must be convertible to a single type. Stuart's shown one way to fix this but that tends to ruin the "expected" sort order when mixing data types. It usually better to do it as two (or more) separate CASE expressions¹ that can then have their natural types (and the other CASE expressions evaluate to NULLs which all sort together)

ORDER BY CASE @OrderBy WHEN 1 THEN exp.PartNo END,
         CASE @OrderBy WHEN 2 THEN exp.ExportDate END
OFFSET @PageSize * (@PageIndex - 1) ROWS

¹You'd normally have one CASE expression for each desired final data type. So if you have 3 possible int columns to sort on, and 2 datetime columns, you'd still just have 2 CASE expressions - one dealing with all of the int columns, and the other dealing with the datetime columns.

sql order by variable with different data type

2 Answers2

Linked