Type of identity returned from SQL Stored Proc

Consider a SQL 2005 Stored Proc which is invoked from C# .net code via a SqlCommand, the stored proc inserts a row and returns the new identity, which is typed as bigint with a statement similar to:

SELECT Scope_Identity()

In the C# code the identity can be read from a call to the SqlCommand ExecuteScalar() method assume the identity in this example is a SQL bigint (which maps to a long / Int64 in dot net).

long myNewId = (long)cmd.ExecuteScalar();
// vb equiv for the cast to long is DirectCast()

(If invoked in the immediate window you’d see : “Cannot unbox 'obj' as a 'long'” )
What's going on?

The problem seems to be that whatever the type of your identity the object returned by ExecuteScalar() is in fact a Decimal, changing the code slightly to:

Object obj = cmd.ExecuteScalar();

Followed by this in debug, immediate window

? obj is System.Decimal

... Will yield "true", so the call is returning a "boxed" decimal, not a long at all… A quick look in the SQL2005-docs online and the return type is simply listed as "numeric".

After some fishing on Google, the consensus across a few posts (one example) seems to be that MS chose to use the largest type possible for the value returned by Scope_Identity() and @@IDENTITY because an identity column could be of various types, smallint, int, bigint, decimal and it made sense to make the return value the largest.

The problem - at least for the unwary developer - is that casting in the usual way (long)cmd.ExecuteScalar() won’t work, as this cast works in a similar way to the VB DirectCast() which doesn’t perform a check for fundamental types. One could argue you should always check the type before trying to unbox.

Note: VB CType() would allow unboxing of any fundamental type, to any other fundamental type, System.Convert() would also work in C#. See this good article - with examples in VB - for more info on boxing.

In the end I worked around this by defining a variable of the correct type in the stored proc and assigning the identity value to that and passing the variable back in the stored proc "select" statement.