Wednesday, October 19, 2011

Identity field - int or not

I have an identity field in my SQL Server database's table. And I have this stored procedure which inserts a row in this table. The stored proc also returns a few other calculated values other than the new id created. So, I have a SELECT query at the end of the stored proc which goes like this

SELECT SCOPE_IDENTITY() as SomeId, @bar as SomethingImportant

And I use this code in C# to extract this id
int id = (int)rows[0]["SomeId"];

This gives me an invalid cast. Upon inspection, I notice that the value returned from the db call is decimal, not int! Why? Because SCOPE_IDENTITY, and even @@IDENTITY and IDENT_CURRENT(tablename) have the return type defined as NUMERIC(38, 0). This causes .Net to consider it as a decimal.

http://msdn.microsoft.com/en-us/library/ms190315.aspx
http://msdn.microsoft.com/en-us/library/ms187342.aspx
http://msdn.microsoft.com/en-us/library/ms175098.aspx

I ended up using it like this
int id = (int)(decimal)rows[0]["SomeId"];

UPDATE: In my second version, I assigned the value from SCOPE_IDENTITY() to a locally declared INT variable in the stored procedure, and returned that instead. That way, the C# code also takes it as in int.

No comments:

Post a Comment