Get @@IDentity before or after commit?

Question

Post reply

Get @@IDentity before or after commit?

sammuts

SSC Veteran

Points: 282
More actions
March 7, 2012 at 10:03 am

#269584

Hi,
I have the procedure shown below. As ou can see I am getting the identity of an auto increment field before the commit transaction. Is this a good practive or should I get the identity after commiting?
BEGIN TRY
BEGIN TRANSACTION
INSERT
INTO ST_Airports
(
[AirportCode],
[AirportName],
[CountryID]
)
VALUES
(
@sAirportCode,
@sAirportName,
@iCountryID
)
SET @iNewID = @@IDENTITY
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @iNewID = 0
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
EXEC spa_HandleError
END CATCH

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

From BOL @@IDENTITY

Is a system function that returns the last-inserted identity value.

Now is that what you want ... ?

I am thinking that you want the value AFTER the insert ....

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

It doesn't make any difference. The value will be available right after the INSERT takes place and you will discard it anyway if you can't reach the commit for any reason (you fall into the CATCH block).

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

Careful folks. @@IDENTITY won't work the way you think it will if a trigger is involved and you should always plan on someone adding such a thing in the future if it doesn't already exist. Use SCOPE_IDENTITY() instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

You're right Jeff (as usual)!

Thanks for pointing it out.

-- Gianluca Sartori

Steve Jones - SSC Editor SSC Guru Points: 738663 More actions · Answer 5

Yep, Jeff is correct. Scope_identity(), and understand that if the next identity value is 12, and the transaction fails, the next one inserted will be 13. The identity increments are not rolled back, which is what Gianluca mentioned.