How do I cast Scope_Identity() to Int? - 【StackMirror】|sql-server|scope-identity

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?

2009-06-16 14:28
by Peter Turner

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()

2009-06-16 14:32
by Jose Basilio

So the cast is taken care of in the process of assigning to NewIdent, but it's still casting - lowerkey 2013-04-17 13:38

It does not work for me, SQL2016, it just return decimal(18,0) for me , I had to convert it to in - Mohammad Joneidi 2017-12-13 10:33

SELECT CAST( bigintcolumn AS int )

(Provided you know it will fit into a 32bit integer)

2009-06-16 14:31
by Mitch Wheat

That's what I thought, I'm not sure why it doesn't work though. T'was returning 0 - Peter Turner 2009-06-16 14:33

Just cast this like:

select CAST(SCOPE_IDENTITY() as int)

And your data Layer:

reader.GetInt32(0);

2017-05-16 13:44
by user8020064