how to show empty value for a int datatype in SQL?

how to show empty value for a int datatype in SQL?

I have a case statement on an int datatype column to show empty for the values less than or equal to 0.

case when [TotalValue] <= 0 Then ''
 when [TotalValue] > 0 Then [TotalValue]
 End as [TotalValue]

Right now, case statement is returning 0 for any values less than or equal to 0. I expect to have them as Empty. Having 0 instead of negative value is not a correct result.

How to convert the record to show only empty?

Answer

The problem of your code is that Then '' is automatically converted to int value, which happens to be 0 for empty strings (try select CAST('' as int) to check).
The data type for ambiguously defined column (like yours) is determined from the data type precedence rules.

Unambiguously defining the data type of the column would resolve the issue.

I recommend trying to return NULL from the database, like this:

case when [TotalValue] <= 0 Then NULL
 when [TotalValue] > 0 Then [TotalValue]
 End as [TotalValue] 

Most likely, your report engine will convert NULL to something like an empty string. In addition, you may be getting some benefits of ability to manipulate numeric values, if your report engine supports those (e.g. calculate average over selection).

Alternatively, try casting the values to string in SQL:

case when [TotalValue] <= 0 Then ''
 when [TotalValue] > 0 Then CAST([TotalValue] as varchar)
 End as [TotalValue]