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?

score 18 · Accepted Answer · answered Jun 16 '09 at 14:32

18

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

answered Jun 16 '09 at 14:32

Jose Basilio

50,714
13
121
117

3

So the cast is taken care of in the process of assigning to NewIdent, but it's still casting. – lowerkey Apr 17 '13 at 13:38
It does not work for me, SQL2016, it just return decimal(18,0) for me , I had to convert it to int – Mohammad Joneidi Dec 13 '17 at 10:33

score 13 · Answer 2 · answered Jun 16 '09 at 14:31

13

SELECT CAST( bigintcolumn AS int )

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

answered Jun 16 '09 at 14:31

Mitch Wheat

295,962
43
465
541

That's what I thought, I'm not sure why it doesn't work though. T'was returning 0. – Peter Turner Jun 16 '09 at 14:33

score 4 · Answer 3 · edited May 16 '17 at 16:07

4

Just cast this like:

select CAST(SCOPE_IDENTITY() as int)

And your data Layer:

reader.GetInt32(0);

edited May 16 '17 at 16:07

davioooh

23,742
39
159
250

answered May 16 '17 at 13:44

user8020064

41
1

How do I cast Scope_Identity() to Int?

3 Answers3

Linked