SQL server @@Identity issue

Question

I'm using @@Identity to get identity of a particular row after successful insert on a table, but sometimes I'm getting someother identity other than what it created for a particular row.

Sample:

ID    UserName    Age    Location
1     Andy        22     USA
2     Robert      24     Canada
3     James       26     Mexico

From above sample while getting identity for user 'Robert' some other created 'James' and I'm getting the lastest identity as '3' instead of '2'.

All this is happening in a ASP.NET website user registration, it's giving the latest identity for each connection object and the same connection object is using for all registrations.

Please some one help me this out how to get the exact identiy when some other people using same connection object to get the identity?

If i use SCOPE_IDENTITY() for the below query, I'm getting DBNULL exception while convertion.

cmd.Connection = conn;
                conn.Open();
                int intStatus = cmd.ExecuteNonQuery();
                if (intStatus == 1)
                {
                    SqlCommand cmd1 = new SqlCommand("Select SCOPE_IDENTITY()", conn);
                    id_package = Convert.ToInt32(cmd1.ExecuteScalar());
                }

possible duplicate of [scope\_identity vs ident\_current](http://stackoverflow.com/questions/567171/scope-identity-vs-ident-current) — Tanner, Jul 20 '15 at 12:39

score 3 · Accepted Answer · answered Jul 20 '15 at 11:03

3

use SCOPE_IDENTITY() it will return the last identity value generated in your session.

@@IDENTITY will return the last generated identity value in ANY session.

Also if you are inserting multiple rows you can use the OUTPUT clause too to get all the newly generated identity values in your session.

answered Jul 20 '15 at 11:03

M.Ali

67,945
13
101
127

Hi Ali, I'm getting DBNULL exception while using the above code(edited) am i doing anything wrong? – nag Jul 20 '15 at 12:15
1

`@@IDENTITY` returns the last identity value in your session but at any scope. – Martin Smith Jul 20 '15 at 12:30

Fabio · Answer 2 · 2015-07-20T11:24:39.177

1

Use OUTPUT Clause

INSERT INTO YourTable (UserName, Age, Location)
OUTPUT inserted.ID
VALUES ("Andy", 22, "USA")

If ID is identity column, statement will return inserted ID by this query

edited Jul 20 '15 at 11:24

answered Jul 20 '15 at 11:18

Fabio

31,528
4
33
72

Support for the `OUTPUT` clause was added in SQL Server 2005. If you have this version or newer, this is the method to use to get a generated key out of an insert, because it runs as a single, atomic statement. – Paul Turner Jul 20 '15 at 11:25
Fabio - Thanks for your reply. I'm getting DBNULL exception while using the above SCOPE_IDENTITY code(edited) am i doing anything wrong here? – nag Jul 20 '15 at 12:16

score 0 · Answer 3 · answered Jul 20 '15 at 11:03

0

I think you are looking for SCOPE_IDENTITY(). This will give you the identity value generated from your query or session

answered Jul 20 '15 at 11:03

Jasqlg

183
1
9

score 0 · Answer 4 · answered Jul 21 '15 at 11:41

I don't know anything about asp.net, but it at least looks to me like you're creating a new database session to get the identity and that's why you get nulls. You'll have to fetch it in the same session where you have created the row (or use output clause).

SQL server @@Identity issue

4 Answers4