April 2, 2003 at 3:04 am
Have hit a bit of stumbler with respect to @@IDENTITY.
After an insert into a table I always want to get the @@IDENTITY of the row just inserted so that I can return it to the user. However, this one particular table has a Trigger on it which creates an entry into another table. When I access the @@IDENTITY immediately after the INSERT I get the @@IDENTITY of the row inserted on the second table and not the first.
Is there a way around this?
Here is the code I am using :-
-- #
-- ### Insert into table
-- #
INSERT INTO dbo.tbl_job_raw
(
field1
,field2
)
VALUES
(
@l_parm_field1
,@l_parm_field2
)
-- #
-- ### Store the @@ERROR and @@ROWCOUNT variables before they are changed
-- #
SELECT @l_SQL_ERROR= @@ERROR, @l_SQL_ROWCOUNT = @@ROWCOUNT
-- #
-- ### Test error condition
-- #
IF @l_SQL_ERROR <> 0
BEGIN
SELECT @l_Error_Code = @lk_ERROR_SEED + 3
SELECT @s_Error_Msg = "Error Code " + STR(@l_SQL_ERROR) + " encountered during Insert"
END
ELSE
BEGIN
-- #
-- ### Set the returned row identifier number
-- #
SELECT @l_parm_output_row_id= @@IDENTITY
END
April 2, 2003 at 3:25 am
Try using IDENT_CURRENT
Have a look in BOL. A few examples included.
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
April 2, 2003 at 5:38 am
I use scope_identity() myself - only in SQL2K.
Andy
April 2, 2003 at 6:42 am
Thanks for assistance.
IDENT_CURRENT seemed to do the trick. Although I was forced to enter the name of the table.
Help much appreciated.
April 2, 2003 at 2:29 pm
Watch out for ident_current though, as it returns the latest identity-value on a table, from any connection. So if you're connection inserts a value, then a second connection inserts a value before you manage to run ident_current you will get the second value. Use scope_ident as Andy suggested instead.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
April 2, 2003 at 7:50 pm
Use SCOPE_IDENTITY in this case. There's a reason it's called that - because it retrieves the ID within the scope of your proc. IDENT_CURRENT should only be used to obtain the ID of the most recent entry in a table (not necessarliy from your proc's insert).
keith
keith
April 2, 2003 at 11:05 pm
SCOPE_IDENTITY it is then.
Once again, many thanks for contributions.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply