How do I cast Scope_Identity() to Int?

sql-server scope-identity
Peter Turner picture Peter Turner · Jun 16, 2009 · Viewed 11.5k times · Source

So Scope_Identity() returns an ?-Byte Numeric Type in SQL Server.

That is not awesome.

Is there a safe way to cast it to an int in a select query so we don't have to manage every whim of SQL Server in our ODBC Wrapper?

Answer

Jose Basilio picture Jose Basilio · Jun 16, 2009

If the source column to which the identity belongs is an Integer, there's no need to cast it. The following works just fine assuming the identity column is an Integer to begin with or it fits inside an "Int".

DECLARE @NewIdent Int
SET @NewIdent = SCOPE_IDENTITY()

Related questions

SCOPE_IDENTITY() for GUIDs?

Can anyone tell me if there is an equivalent of SCOPE_IDENTITY() when using GUIDs as a primary key in SQL Server? I don't want to create the GUID first and save as a variable as we're using sequential GUIDs …

Read More
IDENT_CURRENT returns the last identity value but SCOPE_IDENTITY does not

I have a SQL Server as backend for an asp.net application. Multiple people might insert data in the same table 'the same time...'. When I read the solution/answer from this post: scope_identity vs ident_current THEN …

Read More
Why does select SCOPE_IDENTITY() return a decimal instead of an integer?

So I have a table with an identity column as the primary key, so it is an integer. So, why does SCOPE_IDENTITY() always return a decimal value instead of an int to my C# application? This is really annoying …

Read More