Return id from stored procedure

[marjoh]

Hi!

I have a stored procedure (SQL 2000) in which i make an insert in a table. Some of the columns in the table are:
ID (int)
FileName (varchar)
[Time] (datetime)

The problem is that "ID" is the primary key, and configured as "Identity". Whenever I make an insert, the value of this field is automatically generated. I want to return the number that is generated at insert, but I don't know how to!? I hope someone can help me! Great forum btw...

/M

[HairyMonkeyMan]

There is a scope_identity() function which will get you what you want

see http://msdn.microsoft.com/en-us/library/ms190315.aspx

gl

[erickwidya]

or u can used RETURN @@IDENTITY but it's for all ur scope based on the documentation

[Krzemo]

Never use "RETURN @@IDENTITY" - it is just asking for trouble!



Use only "RETURN SCOPE_IDENTITY()" instead.
Why?

Let us see:

INSERT INTO table1 (a) VALUES ('a')
RETURN @@IDENTITY

Its seams to work OK.
And than U decided to have a trigger defined on table1 which do this:

INSERT INTO table2 (a) select [id] from inserted

And it is also working good.... But ... hmmm ... why [id]s returned from SP are now wrong?
Because U returned global value of identity, not identity which U generated in the scope of your SP. That’s why!

Use "SCOPE_IDENTITY()" always (it was introduced by Microsoft in MsSQL2000 to fix problem with triggers, and Microsoft is keeping @@IDENTITY IMHO only for backward compatibility)

Best regards,
Krzemo.

[erickwidya]

aargh..thanks for that info Krzemo

i never used trigger in my DB, so never run into any trouble ..will change it to SCOPE_IDENTITY() ASAP