pokerplayer23 pokerplayer23 - 3 months ago 11
SQL Question

SQL Server - Case Expression and number formatting

I have a script where I have the below case expression.

The strange thing that is happening is that SQL will cast all the results as Decimal 18,1. Even the metricid 1 records which should be cast as int.

SELECT CASE

WHEN metricid = 1 then cast(result as int)
WHEN metricid = 2 then cast(result as decimal(18,1)

END as column1
from sometable


I even added something like the following as the second condition which should not be evaluated because 1 does not = 3 (right?) but it still converts all the results to decimal:

WHEN 1=3 and metricid = 2 then cast(result as decimal(18,1)


I think I broke the SQLs.... :(

Answer

SQL allows only one data type in a single column. The possible solution might be converting your output to VARCHAR. For example, consider the following example:

DECLARE @metricid INT
DECLARE @result INT

SET @metricid = 2 --(or 1)
SET @result = 20

SELECT CASE WHEN @metricid = 1 then CAST(CAST(@result as int) AS VARCHAR(55))
            WHEN @metricid = 2 then  CAST(CAST(@result as decimal(18,1)) AS VARCHAR(55))
            ELSE ''
       END Result

The return type of a CASE expression is the highest precedence type from the set of types in result_expressions and the optional else_result_expression. Precedence of data types can be checked here.

SQL-Server 2012 +

SELECT CASE WHEN @metricid = 1 then FORMAT(@result, '#')
            WHEN @metricid = 2 then FORMAT(@result, '#.0')
            ELSE ''
       END Result
Comments