returning id from stored prcedure

[jenny]

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

[Lothar Haensler]

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.

[jenny]

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

[Lothar Haensler]

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.

[jenny]

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