• saravanatn - Saturday, October 20, 2018 3:24 AM

    drew.allen - Friday, October 19, 2018 2:15 PM

    This should be handled in the presentation layer, not the database layer.

    SQL Server is strongly typed.  That means that you cannot mix string ('') and decimal data in the same column.  This is why it's converting your '' to 0.

    Drew

    I am getting the answer directly(without any conversion) in sql server 2017.

    https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ad34673979ff21b187a4b490952990f6

    When I tried in sql server 2008 I got conversion error .My question is different version of sql server behaves differently??

    That CASE won't return a conversion in SQL Server 2008 either; there is only one data type being returned a varchar(1), which is the value '' (the NULL will be implicitly cast to a varchar(1), as it doesn't have a defined datatype when you declared it).

    I think you're misunderstanding how a CASE expression works. If you have the expression:
    CASE WHEN A IS NULL THEN '' END
    The above can only return 2 values. The first value is '', which is when A has a value of NULL. Otherwise NULL will be returned, as no expressions within the CASE evaluated to TRUE. To return the value of A is it isn't NULL you would use:
    CASE WHEN A IS NULL THEN '' ELSE A END
    However, then you would likely be better off using ISNULL:
    ISNULL(A,'')
    This, however, will still suffer conversion errors for what you have.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk