SELECT cc.[Value] FROM [COMMON].[Parameters].[tCubeColumn] cc INNER JOIN COMMON.[Parameters].tCube c ON cc.rCube=c.Id ORDER BY (CASE WHEN c.IsInsuranceLength=1 THEN CONVERT(INT, cc.Value ) ELSE cc.Value END) Is this code valid or not? Facts are that cc.[Value] is of VARCHAR data type and it contains both integer and non-integer kind of data.

188.

SELECT
cc.[Value]
FROM [COMMON].[Parameters].[tCubeColumn] cc
INNER JOIN COMMON.[Parameters].tCube c ON cc.rCube=c.Id
ORDER BY

(CASE WHEN c.IsInsuranceLength=1 THEN CONVERT(INT, cc.Value )
ELSE cc.Value
END)

Is this code valid or not? Facts are that cc.[Value] is of VARCHAR data type and it contains both integer and non-integer kind of data.

Invalid code - CASE statements can't be used in ORDER BY clause

Invalid code - CONVERT function will fail

Valid code

View Description

The convert function is going to fail because in CASE statements all possibilites are being evaluated for each line. There when you try to convert a varchar value of non-numeric nature to INT, the code fails. To do such custom sorting, CROSS APPLY can be used or the code can be placed within a function