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
Re: returning id from stored prcedure
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.
Re: returning id from stored prcedure
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
Re: returning id from stored prcedure
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.
Re: returning id from stored prcedure
Hi and thanks Lothar!
The cut and paste went to quickly and the names of the varibles was wrong.... Of course you´r