Scope Identity - 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)

Scope Identity

Author

Topic

JTProg
Starting Member

24 Posts

Posted - 2006-12-13 : 15:58:46

I have three insert commands in a stored procedure. After two of the insert commands I am calling the scope identity, which is used in the next insert statement. The scope identities are working just fine, but I don't know if this is the proper way of using the scope identity and if this has the potential of causing errors when multiple people hit that stored procedure. Would it be better to call different procedures, or to leave it as is?

Here is some of the code from the procedure:

DECLARE @ScopedValue int
DECLARE @ScopedValueFormID int

INSERT INTO dbo.tblNewBatch
( UserName,
Action,
Type)

VALUES
( @UserName,
@Action,
'Professional')

SELECT @ScopedValue = SCOPE_IDENTITY()

INSERT INTO dbo.tblProfessionalTec
( TMID,
LongDescription,
ShortDescription)

VALUES
( @ScopedValue,
@LongDescription,
@ShortDescription)

SELECT @ScopedValueFormID = SCOPE_IDENTITY()

INSERT INTO dbo.tblPlans
( FormID,
PlanID,
Allowed)

VALUES
( @ScopedValueFormID,
'Default',
1)

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 16:03:42

It's fine.

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-13 : 16:23:30

To elaborate a little more...SCOPE_IDENTITY() will return the identity value of the previous INSERT statement. It doesn't matter if multiple people are hitting the same code as SCOPE_IDENTITY() works on the session.

Tara Kizer

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-13 : 17:04:58

A bit of reading from BOL

quote:

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

Kristen
Test

22859 Posts

Posted - 2006-12-14 : 01:50:33

Some error checks (including a test for @@ROWCOUNT = 1) after each INSERT, and a transaction block - which you rollback if an error occurs - might be prudent though!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources