What type is this: ISNULL(SUM(COALESCE (a.currency, 0)), 0)

Question

I have the following piece of sql query:

Sql += " SELECT     ISNULL(SUM(COALESCE (a.currency, 0)), 0) AS monthCurrency              
FROM          IW_Awards AS a ";

The query works fine, I am trying to cast this value in C# after the query has been submitted and I cannot find what type it is.

I tried int, int? and string. When I tried to cast to a string I had the following error message: Unable to cast object of type 'System.Decimal' to type 'System.String'.

And when I tried to cast it to an Int or Int? I got: Specified cast is not valid.

Database: SQL IDE: Visual Studio 2010 Database interface: LINQ (but I am creating my own query) Server side language: C#

Thank you for your help.

Answer 1

Did you try decimal? It told you it was trying to cast a System.Decimal to a string when you tried to cast it to a string.

It will return whichever type Currency is.

Answer 2

It sounds like it's a decimal. That's what the error message is telling you, you're trying to cast a decimal to a string.

Answer 3

What type is a.currency? I'd say this is probably a decimal datatype from your error message.

Out of curiousity why use coalesce and isnull? Since you have already changed all null values to 0, you shouldn't need the outside isnull at all.

Answer 4

If currency is an int in the database, then use Convert.ToInt32. Another option is to use myTable.Rows[0].Field<int>("monthCurrency") from the System.Data namespace. The problem is sometimes even though you return an int from SqlServer, .NET still thinks it's a decimal.

Answer 5

It appears to me that the result is a decimal, based on the error message that specifies that it cannot convert from System.Decimal.

Answer 6

As others mentioned, it is decimal that you are looking for, but I want to add this link to the CLR-SQL type mapping documentation (very useful link).