Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Retrieve id of newly added row in stored procedure

Author  Topic 

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-05 : 12:04:21
Anyone knows how to get the newly added row's id and use it in another stored procedure? Is that possible? If not, how do i return the id of the newly added row? Thanks

jaroot
Starting Member

46 Posts

Posted - 2006-01-05 : 12:18:07
Directly below your insert..
SET @tempID = @@Identity
then..
EXEC storedproc @tempID

Be careful that your insert doesn't fire off any triggers that do any inserts/updates. That will throw off your @@Identity value
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-05 : 12:21:12
...or use GUIDs and set your ID values BEFORE you insert the record.
Go to Top of Page

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-05 : 12:37:53
I did mine as shown below. I do not quite get "EXEC storedproc @tempID". "storedproc" means another stored procedure?


CREATE PROCEDURE [ADDPROMOTION]
(
@PROMOTIONNAME VARCHAR (100),
@PROMOSTARTDATE DATETIME,
@PROMOENDDATE DATETIME,
@DISCOUNTRATE INT,
@PROMODESC VARCHAR(100)
)

As
-- INSERT the new record
INSERT INTO
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@PROMOTIONNAME, @DISCOUNTRATE, @PROMOSTARTDATE, @PROMOENDDATE, @PROMODESC)

-- Now return the InventoryID of the newly inserted record
SELECT SCOPE_IDENTITY()
GO


quote:
Originally posted by jaroot

Directly below your insert..
SET @tempID = @@Identity
then..
EXEC storedproc @tempID

Be careful that your insert doesn't fire off any triggers that do any inserts/updates. That will throw off your @@Identity value

Go to Top of Page

TimS
Posting Yak Master

198 Posts

Posted - 2006-01-05 : 12:38:02
May be better to use SCOPE_IDENTITY() if you have a trigger on the table.
SET @tempID = SCOPE_IDENTITY()


Tim S
Go to Top of Page

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-05 : 13:00:59
In the query window, how do i retrieve the @tempID value?

quote:
Originally posted by TimS

May be better to use SCOPE_IDENTITY() if you have a trigger on the table.
SET @tempID = SCOPE_IDENTITY()


Tim S

Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-05 : 13:17:16
>>how do i return the id of the newly added row?
There is basically 3 ways to get an integer value from a stored procedure:

1. SELECT it (as you did above)

2. RETURN it (only works for ints. typically RETURN should be used to indicate success/failure of the SP. "0" would indicate success.

3. OUTPUT variable
This is a good choice because it's simple for using with an other stored procedure and it's efficient for when calling from an application because you don't need to look at the record set.


Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-05 : 13:24:26
Here is an example of using an output variable to get the new identity value from a stored procedure:

use northwind
set nocount on
go
create table junk
(junkid int identity(1,1) primary key
,i int)
go

create proc junk_ins
@i int
,@junkid int = null output
as

insert junk (i)
values (@i)
set @junkid = scope_identity()
go

declare @newjunkid int
exec junk_ins
@i = 9999
,@junkid = @newjunkid output

select @newjunkid [output from SP]

select * from junk

go
drop proc junk_ins
drop table junk


Be One with the Optimizer
TG
Go to Top of Page

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-05 : 14:10:40
I did this and it worked, please provide me with any comments on how i can further improve it. Thanks

CREATE PROCEDURE [ADDPROMOTION] 
(
@PROMOTIONNAME VARCHAR (100),
@PROMOSTARTDATE DATETIME,
@PROMOENDDATE DATETIME,
@DISCOUNTRATE INT,
@PROMODESC VARCHAR(100)

)

As

Declare @PID INT

-- INSERT the new record
INSERT INTO
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@PROMOTIONNAME, @DISCOUNTRATE, @PROMOSTARTDATE, @PROMOENDDATE, @PROMODESC)
-- Now return the InventoryID of the newly inserted record
SET @PID = SCOPE_IDENTITY()

SELECT @PID
GO
Go to Top of Page
   

- Advertisement -