How do I cast Scope_Identity() to Int?

Question

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?

Dharma

Answer 1

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

Clad

So the cast is taken care of in the process of assigning to NewIdent, but it's still casting. – Cofferdam

It does not work for me, SQL2016, it just return decimal(18,0) for me , I had to convert it to int – Juryrigged

Answer 2

SELECT CAST( bigintcolumn AS int )

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

Csc

That's what I thought, I'm not sure why it doesn't work though. T'was returning 0. – Dharma

Answer 3

Just cast this like:

select CAST(SCOPE_IDENTITY() as int)

And your data Layer:

reader.GetInt32(0);

Carlsbad