| Author |
 Topic  |
|
|
shaoen01
Yak Posting Veteran
78 Posts |
 Posted - 01/05/2006 : 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 - 01/05/2006 : 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 |
Edited by - jaroot on 01/05/2006 12:19:12 |
 |
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 01/05/2006 : 12:21:12
|
...or use GUIDs and set your ID values BEFORE you insert the record.  |
|
|
|
shaoen01
Yak Posting Veteran
78 Posts |
Posted - 01/05/2006 : 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
|
|
|
|
TimS
Posting Yak Master
USA
198 Posts |
Posted - 01/05/2006 : 12:38:02
|
May be better to use SCOPE_IDENTITY() if you have a trigger on the table.
SET @tempID = SCOPE_IDENTITY()
Tim S |
|
|
|
shaoen01
Yak Posting Veteran
78 Posts |
Posted - 01/05/2006 : 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
|
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5467 Posts |
Posted - 01/05/2006 : 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 |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5467 Posts |
Posted - 01/05/2006 : 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 |
|
|
|
shaoen01
Yak Posting Veteran
78 Posts |
Posted - 01/05/2006 : 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
|
|
|
| |
Topic |
|
Retrieve id of newly added row in stored procedure
