Friday, November 03, 2006

SCOPE_IDENTITY() and ADO.Net

T-SQL's SCOPE_IDENTITY() always returns a "Numeric" value (this is a decimal).
 
If you're using ADO.NET, then you've got two choices:
 
Cast it as to the correct type in the consuming code:
(int) reader.GetDecimal
 
Cast it as an int in the T-SQL:
CAST ( SCOPE_IDENTITY() As int )
 
I'd favour the method second, as other DBs might not have this nuance.

1 comment:

Adam said...

If your identity column is a bigint (64 bits), then you don't want to cast to an int (32 bits) in the client code. Even better, you can't directly cast a Decimal to an Int64 in C#.