return IDENTITY after SQL Insert?

G

Guest

I'm using an SQLCommand to insert row using a text command. Is there a way to
return the IDENTITY key value after the insert ?

Thanks much!
 
M

Mischa Kroon

Dabbler said:
I'm using an SQLCommand to insert row using a text command. Is there a way
to
return the IDENTITY key value after the insert ?

set SQLCommand =
insert into table values ('string value');select scope_identity()
 
N

Naveen

Naveen Bhardwaj


This can be done by :-

using @@IDENTITY which returns the last-inserted identity value

But the preferable solution is to use IDENT_CURRENT('TableName') which
returns the last identity value generated for a specified table in any
session and any scope.
 
K

Karl Seguin [MVP]

In case anyone is curious, @@IDENTITY is dangerous 'cuz it returns the last
ID generated from any scope. If you are using triggers, look for this to
really bite you in the ass.

For example, you insert a new customer and select @@IDENTITY to get his/her
customerId...problem is you have a trigger on insert in your custom table
which adds a new audit record. You'll end up getting hte auto-generated
auditId wthout knowing it..

Karl
 
T

tfsmag

I had a similar issue to what Karl mentioned, was lucky enough to catch
it while still in development. I ended up doing away with using
triggers for auditing purposes and just made the audit table insert at
the end of the stored proc.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top