How to convert varchar to decimal when no row data exists

I have a t-sql statement that sums up varchar values by converting them to decimals. If no values exist, to return 0.

The error I am receiving is "Error converting data type varchar to numeric". This occcurs only when no result data is returned.

select sum(convert(decimal(28,8), isnull(myColumn, 0)))
from myTable
//...multiple inner joins
//...and multiple filter criteria

As no data exists the workaround I was thinking of was doing a select count to detect if any rows exist and then do the following statement. Are there any b开发者_JS百科etter solutions for this?

All below answers do not work when NO DATA exists. Could not find any other way of doing other than doing a if exists.

This was part of a lot larger sql script and this specific part was updating a temp table.

if exists(select * from table inner join...where...)
being
    select sum(convert(decimal(28,8), isnull(myColumn, 0)))
    from myTable
    ...inner joins
    ...where clause
end

Update

The actual reason why it was erroring was because of the where clause where the conversion was happening, and erroring, on the joins before the where clause was being run. The inner joins were returning null values.

Your isnull is in the wrong place.

sum(convert(decimal(28,8),isnull(myColumn,0)))

Convert the null value to 0 then convert to decimal and then finally sum.

sum(convert(decimal(28,8), isnull(myColumn,'0'))

Just for the record, some prefer COALESCE:

sum(convert(decimal(28,8), COALESCE(myColumn,'0'))

I hope this query could help you

select isnull(sum(convert(decimal(28,8),isnull(myColumn,0))), 0)
from myTable