CODEWITHSUNDEEP
c#sql-server-2008castingcoalesce
Slinky
Slinky

Reputation: 5832

Proper Way to CAST a Numeric as an INT in SQL Server

I am getting an invalid cast exception because my COALESCE is handling a numeric datatype value and also an int datatype value.

The query runs fine but C# is throwing an invalid cast exception.

I added a cast to my COALESCE but still getting the invalid cast exception.

Question: Am I casting this properly in my COALESCE?

The property involved in the error is declared as follows:

public int  StartingMembershipCount { get; set; }

The place where the error occurs is here

 m.StartingMembershipCount = r.GetNullableInt32("StartingMembershipCount",0);

My data helper is defined as:

public static int GetNullableInt32(this IDataRecord r, string name, int defaultValue)
{
    var ordinal = r.GetOrdinal(name);
    int returnVal = 0;
    if (r.IsDBNull(ordinal))
         returnVal = defaultValue;
    else
       returnVal = r.GetInt32(ordinal);


    return returnVal;
}

Here is my COALESCE (expression 1 has the numeric datatype)

COALESCE((
-- Expression 1
SELECT CAST(n.TOTAL_MBR AS INT)
FROM
usr_kiw_cus_certified n INNER JOIN
(SELECT ClubId, min(AsOfDate) AsOfDate FROM
  (SELECT ukm.MASTER_CUSTOMER_ID ClubId, ukm.AS_OF_DATE AsOfDate FROM usr_kiw_cus_certified ukm WHERE ukm.AS_OF_DATE > @StartDate AND ukm.master_customer_id = c.MASTER_CUSTOMER_ID) as temp
 GROUP BY ClubId) AS datetable 
 ON datetable.ClubId = n.MASTER_CUSTOMER_ID and n.AS_OF_DATE = datetable.AsOfDate
 ),

 -- Expression 2
(SELECT COUNT(cr.MASTER_CUSTOMER_ID) 
FROM cus_relationship cr 
WHERE cr.RELATED_MASTER_CUSTOMER_ID = c.MASTER_CUSTOMER_ID
AND cr.RELATIONSHIP_TYPE = 'employment' 
GROUP BY cr.RELATED_MASTER_CUSTOMER_ID
)) AS StartingMembershipCount,

Upvotes: 2

Views: 298

Answers (1)

Marc Shapiro
Marc Shapiro

Reputation: 571

You have to cast the entire expression:

CAST(COALESCE((SELECT ...),(SELECT ...)) AS INT) AS StartingMembershipCount,

When no rows are returned from the subqueries, the CAST (or type) in the SELECT does not apply.

Or you can "just" cast the entire first subquery, since the second should always return a result:

COALESCE(CAST((SELECT ...) AS INT), (SELECT ...)) AS StartingMembershipCount,

Because the second always returns a result, this really doesn't need to be NULLable at all. Using ISNULL should fix that:

ISNULL(CAST((SELECT ...) AS INT), (SELECT ...)) AS StartingMembershipCount,

The disadvantages of ISNULL are that it can't support a third argument should you need one; and it's not standard. The advantage is that the result is not NULLable, so you can use GetInt32 directly.

By the way, when I get that exception in C#, I usually temporarily switch to r.GetValue(ordinal) to return the value as an object (which, of course, you can't assign to an int variable), and then inspect its type.

Upvotes: 1

Related Questions