best way to retrieve identity after insert

I have multiple processes inserting rows into a table. To ensure row uniqueness I have set a column "userID" as identity and the primary key. I always need to retrieve the newly created "userID" value immediately after the insert. What is the best way to get at this value if no other part of the row is unique?
Immediately after the insert I can use SELECT max(userID) to get this, but what if a different process has inserted another record in the interim?
Thanks in advance. -bigpow

Can you do this programmitically in VB?

------------
Brian J. Smith at 5/3/99 4:06:42 PM

Check BOL and @@identity.


------------
bigpow at 4/30/99 7:57:42 PM

I have multiple processes inserting rows into a table. To ensure row uniqueness I have set a column "userID" as identity and the primary key. I always need to retrieve the newly created "userID" value immediately after the insert. What is the best way to get at this value if no other part of the row is unique?
Immediately after the insert I can use SELECT max(userID) to get this, but what if a different process has inserted another record in the interim?
Thanks in advance. -bigpow

If you're working with SPs, use output parameters.

create procedure TestID(@ID int output)
as
insert into ident_tab
values('Test')

select @ID = @@Identity

return
GO

...Then in VB, when you bind to the SP, define the parameter as rdParamOutput, or adParamOutput (depending on whether or not you're using ADO or RDO.)


------------
patrick beagan at 5/4/99 12:54:55 PM


Can you do this programmitically in VB?

------------
Brian J. Smith at 5/3/99 4:06:42 PM

Check BOL and @@identity.


------------
bigpow at 4/30/99 7:57:42 PM

I have multiple processes inserting rows into a table. To ensure row uniqueness I have set a column "userID" as identity and the primary key. I always need to retrieve the newly created "userID" value immediately after the insert. What is the best way to get at this value if no other part of the row is unique?
Immediately after the insert I can use SELECT max(userID) to get this, but what if a different process has inserted another record in the interim?
Thanks in advance. -bigpow

..but if the tabel has a insert trigger, than @@IDENTITY will return NULL (bug in sql7?)
//LESZEK


------------
Brian J. Smith at 5/4/99 12:58:17 PM

If you're working with SPs, use output parameters.

create procedure TestID(@ID int output)
as
insert into ident_tab
values('Test')

select @ID = @@Identity

return
GO

...Then in VB, when you bind to the SP, define the parameter as rdParamOutput, or adParamOutput (depending on whether or not you're using ADO or RDO.)


------------
patrick beagan at 5/4/99 12:54:55 PM


Can you do this programmitically in VB?

------------
Brian J. Smith at 5/3/99 4:06:42 PM

Check BOL and @@identity.


------------
bigpow at 4/30/99 7:57:42 PM

I have multiple processes inserting rows into a table. To ensure row uniqueness I have set a column "userID" as identity and the primary key. I always need to retrieve the newly created "userID" value immediately after the insert. What is the best way to get at this value if no other part of the row is unique?
Immediately after the insert I can use SELECT max(userID) to get this, but what if a different process has inserted another record in the interim?
Thanks in advance. -bigpow