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 = @@Identitythen..EXEC storedproc @tempIDBe careful that your insert doesn't fire off any triggers that do any inserts/updates. That will throw off your @@Identity value |
|
|
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. |
|
|
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 recordINSERT INTO MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,PROMOENDDATE, PROMODESC)VALUES(@PROMOTIONNAME, @DISCOUNTRATE, @PROMOSTARTDATE, @PROMOENDDATE, @PROMODESC)-- Now return the InventoryID of the newly inserted recordSELECT SCOPE_IDENTITY()GO quote: Originally posted by jaroot Directly below your insert..SET @tempID = @@Identitythen..EXEC storedproc @tempIDBe 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
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 |
|
|
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
|
|
|
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 variableThis 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 OptimizerTG |
|
|
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 northwindset nocount ongocreate table junk (junkid int identity(1,1) primary key ,i int)gocreate proc junk_ins @i int ,@junkid int = null outputasinsert junk (i)values (@i)set @junkid = scope_identity()godeclare @newjunkid intexec junk_ins @i = 9999 ,@junkid = @newjunkid outputselect @newjunkid [output from SP]select * from junkgodrop proc junk_insdrop table junk Be One with the OptimizerTG |
|
|
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. ThanksCREATE PROCEDURE [ADDPROMOTION] (@PROMOTIONNAME VARCHAR (100),@PROMOSTARTDATE DATETIME,@PROMOENDDATE DATETIME,@DISCOUNTRATE INT,@PROMODESC VARCHAR(100))AsDeclare @PID INT-- INSERT the new recordINSERT INTO MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,PROMOENDDATE, PROMODESC)VALUES(@PROMOTIONNAME, @DISCOUNTRATE, @PROMOSTARTDATE, @PROMOENDDATE, @PROMODESC)-- Now return the InventoryID of the newly inserted recordSET @PID = SCOPE_IDENTITY()SELECT @PIDGO |
|
|
|