@@IDENTITY Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

@@IDENTITY Problem

Author

Topic

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2003-06-04 : 14:27:24

Hello all! I am wondering if any of you have come across this and if so, how did you work around it.

I have a stored procedure that is inserting data into multiple tables. Once data gets dumped in, I set a temporary variable. So for instance, once my Person gets added I have SET @intPersonID = @@IDENTITY.

As I mentioned before, my stored proc is doing multiple dumps into tables and at the end of each one I set a variable equal to @@IDENTITY.

Here is what I have come across though. Lets say that my first table is Person and @intPersonID is now set to @@IDENTITY. Now I move on to the Employee table and @intEmployeeID is now set to @@IDENTITY. Then I move onto the relatives table and my insert fails. Is @@IDENTITY holding on to Employee ID? According to books online, it won't revert back to a previous value if an insert fails. I read that as saying that each time I enter into a new Insert statement, @@IDENTITY will be cleared out.

This does not appear to be the case though. After about 4 hours of searching through my tables we determined that @@IDENTITY was in-fact holding on to the value it was set at right before the script failed.

So, do I need to wrap all of my inserts into their own Transactions? Would that ensure that @@IDENTITY is cleared out? Any help would be greatly appreciated.

Thanks all!

Aj

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-06-04 : 14:43:14

Wrapping your inserts in transactions won't affect the value of @@idenitity

 
use tempdb
go
create table foo1
( col1 int identity(1,1), name1 int )
go
create table foo2
( col2 int identity(10, 10), name2 int )
go
create table foo3
( col3 int identity(100, 100), name3 int )
go
begin tran
insert foo1 values(3)
select @@identity
commit tran
go
begin tran
insert foo2 values(5)
select @@identity
commit tran
go
begin tran
insert foo3 values('error') -- this will fail
select @@identity
commit tran
go
select @@identity

Even with the transactions @@identity retains 10,
the value of the last successful insert into a table with an identity column.

Justin

"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!"

Edited by - justinbigelow on 06/04/2003 14:43:48

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2003-06-04 : 14:59:38

So would you recommend that we check @@ERROR after each Insert?

Even then, do we trust @@ERROR to be accurate?

Aj

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-04 : 15:27:04

You should check @@error after each statement.
That's how you can tell whether or not @@identity reflects the addition of a record or not - and also whether to continue processing.

Note that the current value for the identity will be updated before the insert so if the insert fails there will be gaps in the sequence.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2003-06-04 : 17:47:34

So what you are saying is that if I am about to insert into the Person table and I have record ids of 1, 2 and 3, when the insert is about to happen record id 4 is secured for the insert. If that insert fails, then the Person table does not get record id 4 back, it just skips it and moves on to record id 5?

That would explain the missing primary key records. We noticed a gap in them when these procs would fail.

We thought someone was flubbling with our data!

Thanks all!

Aj

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-04 : 18:31:05

Yep.
identities just take the nest value from the current seed.
They don't guarantee to be sequential or unique.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2003-06-05 : 12:29:58

Let me continue to suck knowledge from your brain...

After each insert, I will just do a IF @@ERROR < 0 statement. If that is the case, would you recommend a GoTo function?

I have never used them before, but I have seen them. I just did not know if it was more efficient to use goto or to just stick a RETURN 0 in after my IF block.

Aj

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-05 : 13:12:58

I don't think @@Error returnsa less than 0...could be wrong, but I think you want @@Error <> 0.

Also I would reccomend assigning it to variable right after to execution, because as soon as another statement is executed it's reset.

DECLARE @Error int, @Results int

...some SQL Statement

SELECT @Error = @@ERROR, @Results = @@ROWCOUNT

IF @Error <> 0
BEGIN
Set up some messages
GOTO Error:
END

IF @Results <> 0 -- This is a business logic error, for example I expected some result other no rows
BEGIN
Set up some messages
GOTO Error:
END

...continue merrily along with code

Exit:

Housekeeping procedures

Return 0

Error:

Error routines

RAISEERROR

GOTO Exit

At least that's like something I do.

Brett

8-)

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2003-06-05 : 17:00:29

Thanks all! This is terrific stuff!

And I promised myself I would not cry too!

Aj

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-06 : 08:55:21

Actually, I've got to correct something here (again...)

[homer]doooh[/homer]

I did this typing (what's that) from memory...which of course is fading day by day...

That's why cut and paste is your friend..

Anyway, enough babbling...

quote:

Exit:

Housekeeping procedures

Return 0

Is WRONG

You need to set up a local variable

DECALRE @ReturnCode int
SELECT @ReturnCode = 0

And in the Error trapping set it to some value ( I usually use -1
..but you can make it more meaningful..I use messages for meaning)

So that the common exit will identify success or failure...

(for MR. Mist..a bovine)

MOO

Brett

8-)

Edited by - x002548 on 06/06/2003 08:56:40

Edited by - x002548 on 06/06/2003 08:57:46

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2003-06-06 : 09:25:45

For the additional logic, I thank you.

Now I just have to find an ounce of motivation on a beautiful Friday afternoon here in Topeka Kansas, USA to start implementing it!

*yawn*

*stretch*

*scratch*

Aj

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-06 : 09:32:11

Or, you could just hit the pool with a

ski

Brett

8-)

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2003-06-06 : 13:33:43

Ah, good idea. But no pool. You see ajthepoolman is me just hanging onto my forgotten youth! I used to work as a pool service guy with tons of hot chicks around me and an awesome tan (at least that is how I remember it).

But my wife said that I should go to school and get into computers. Since I am not allowed to argue with my wife, I enrolled. Now I stare out of my window and watch the clouds drift by!

Now I am crying again!

Aj

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-06 : 15:03:36

Funny thing having to pay the bills....

LOL though...

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources