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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy