Correct use of scope_identity?

Question

Correct use of scope_identity?

Mithrandir

SSCrazy

Points: 2628
More actions
March 25, 2004 at 10:51 am

#107491
Hi, I'm creating a SP to insert some records; but to perform the 2nd Insert I need to get the identity field value from 1st insert statement (foreign key).
Searching I get the conclussion to use SCOPE_IDENTITY() instead of @@Identity.
```
CREATE spMySP(@a int, @b int, @c int)AS
BEGIN TRAN

INSERT INTO dbo.MyTable VALUES (@a, @b)
IF @@Error ROLLBACK TRAN

INSERT INTO dbo.MyDetailTable VALUES (SCOPE_IDENTITY(), @c)
IF @@Error ROLLBACK TRAN

COMMIT TRAN
GO
```
What do you think; is this code OK in all senses or what I've missed? (concurrency, "elegancy", maybe performance, etc.)
Thanks
March 29, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

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

fhanlon SSChampion Points: 13131 More actions · Answer 1

Your use of scope_identity() is correct.

Your IF statements are syntactically wrong. You need to compare @@error to something. I assume you want to check if the error is non zero and you likely want to terminate the script if an error is detected. Try

IF @@Error <> 0 BEGIN

ROLLBACK TRAN

RETURN (@@ERROR)

END

I would suggest your insert statements include the fields in which you want to insert :

INSERT INTO dbo.MyTable (col1, col2) VALUES (@a, @b-2)

Also your create statement should indicate what you are creating

CREATE PROC dbo.spMySP(@a int, @b-2 int, @C int)

Francis

Mithrandir SSCrazy Points: 2628 More actions · Answer 2

This was an "on the fly" example to explain my situation, thats why are some errors. 😛

But my main doubt was about scope_identity(), I originally planned to use @@Identity, but reading BOL saw that.

Thanks.

fhanlon SSChampion Points: 13131 More actions · Answer 3

Scope_Identity is a better idea even in a simple example like this. @@identity could be made to work, but a problem could arise if you added a trigger to the first table. @@Identity might then cease to work, while scope_identity() would continue to be fine.

Francis