returning id from stored prcedure

Hi!
I have a stored procedure that will insert a new record into a table. It has an id that the identity an therefore assigned a value by SQLServer automatically at insert.
My problem is that I would like to return that id to my application as soon as the insert is done. How can I do that? The input parameters are not unique so I don´t want to use them as search objects...

Code snippet:

CREATE PROCEDURE stAddNewBP

@name as varchar,
@des as varchar

AS

insert into tbBP
(tbName,tbDescription)
values(@name,@des)

How can I select/return the new ID from this? Please help me. Using SQL Server 7.0 and VB 6.0.
Jenny

here is one of more possible solutions:

CREATE PROCEDURE stAddNewBP

@name as varchar,
@des as varchar,
@newid int OUTPUT

AS

insert into tbBP
(tbName,tbDescription)
values(@name,@des)
select @newid = @@IDENTITY


@@IDENTITY is a system variable that is set after an insert.

Hi Lothar and thanks for your quick reply!

Now there is one more problem. How do I catch it in my application?
when I use :
id = DataEnv.stAddNewBP(Inp(0).Text, Inp(2).Text, DataEnv.rsstGetAllBPGroups!tbBPGroupID, id)
it returns 0 for successful execution(I guess).
What am I missing here? How can I use it? In query analyzer everything works just perfect it is now the app that I don´t know how to handle.

could you or somebody else help me again?

Jenny

your code looks strange to me. you use the variable "id" for storing the return value as well as argument for the SP.
I'd rather code like this:

dim id as ???
dim idNew as Long
id = DataEnv.stAddNewBP(Inp(0).Text, Inp(2).Text, DataEnv.rsstGetAllBPGroups!tbBPGroupID, idNew)



after successful execution idNew should contain the new identity value.

Hi and thanks Lothar!
The cut and paste went to quickly and the names of the varibles was wrong.... Of course you´r