Data set with Null & Decimal values for Report

1

I have two tables join in Store procedure when run one data column name Amount look like the following

Amount
100
500
NULL
250
I need to create a report and need a SQL statement that will allow me to convert the data
If Amount IS null display 'ERROR' if not Display Amount, I've tried several IIF and CASE statement but all return Error converting data type varchar to numeric. I need Amount to be in Decimal so I can format it like the following for the report.. Any help will be appreciated.. Thanks

$100.00
$500.00
ERROR
$250.00

2

CASE WHEN Amount IS NULL THEN 'ERROR' ELSE FORMAT(Amount,'$###,###.00') END

Recommended best practice is to format the data where it is consumed, for example in the UI layer, rather than in the database.

1 Like
3

Thank you , really appreciate you responding.

4

Or:
ISNULL(FORMAT(Amount,'$###,###.00'), 'ERROR')