Return id from stored procedure
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
Re: Return id from stored procedure
There is a scope_identity() function which will get you what you want
see http://msdn.microsoft.com/en-us/library/ms190315.aspx
gl ;)
Re: Return id from stored procedure
or u can used RETURN @@IDENTITY but it's for all ur scope based on the documentation
Re: Return id from stored procedure
Never use "RETURN @@IDENTITY" - it is just asking for trouble!
;)
Use only "RETURN SCOPE_IDENTITY()" instead.
Why?
Let us see:
Code:
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:
Code:
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.
Re: Return id from stored procedure
aargh..thanks for that info Krzemo
i never used trigger in my DB, so never run into any trouble :D..will change it to SCOPE_IDENTITY() ASAP ;)