SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Retrieve id of newly added row in stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shaoen01
Yak Posting Veteran

78 Posts

Posted - 01/05/2006 :  12:04:21  Show Profile  Reply with Quote
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 - 01/05/2006 :  12:18:07  Show Profile  Send jaroot an AOL message  Reply with Quote
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

Edited by - jaroot on 01/05/2006 12:19:12
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 01/05/2006 :  12:21:12  Show Profile  Reply with Quote
...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 - 01/05/2006 :  12:37:53  Show Profile  Reply with Quote
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

USA
198 Posts

Posted - 01/05/2006 :  12:38:02  Show Profile  Reply with Quote
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 - 01/05/2006 :  13:00:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 01/05/2006 :  13:17:16  Show Profile  Reply with Quote
>>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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 01/05/2006 :  13:24:26  Show Profile  Reply with Quote
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 - 01/05/2006 :  14:10:40  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.22 seconds. Powered By: Snitz Forums 2000