where can i capture @@IDENTITY? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

where can i capture @@IDENTITY?

Author

Topic

michaelxvo
Starting Member

47 Posts

Posted - 2006-05-04 : 16:44:13

I would like to know where to put the code to capture the @@identity:
assuming table order having column orderid with IDENTITY ACTIVATED.

DECLARE @ID INT
BEGIN TRAN
insert into order(ordername,date)
values (@ordername,getdate())
SHOULD I PUT SELECT @ID = @@IDENTITY HERE
COMMIT TRAN
OR SHOULD I PUT SELECT @ID = @@IDENTITY HERE.

THANK YOU IN ADVANCE

Kristen
Test

22859 Posts

Posted - 2006-05-04 : 17:00:19

[code]
DECLARE @ID INT
BEGIN TRAN
insert into order(ordername,date)
values (@ordername,getdate())
SELECT @ID = SCOPE_IDENTITY()
COMMIT TRAN
[/code]
but I don't think your BEGIN TRANSACTION / COMMIT is doing anything useful - unless you check the @@ERROR or @@ROWCOUNT and do a ROLLBACK if there is a problem - or unless you are going to add some additional insert/update/delete statements within the transaction block (or, I suppose, unless you have a trigger on your ORDER table)

Kristen

michaelxvo
Starting Member

47 Posts

Posted - 2006-05-04 : 17:34:57

IN ACTUAL CODES, I HAVE MORE THAN ONE STATEMENT TO PROCESS SO I WOULD LIKE TO BIND THEM TO PROCESS EITHER ALL OR NOTHING.
IN STEAD OF WRITING
SELECT @PROVIDERID = SCOPE_IDENTITY()(YOUR SUGGESTION)
CAN I WRITE
SELECT @PROVIDERID = @@IDENTITY

THANK YOU

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-04 : 17:39:16

@@IDENTITY can be inaccurate. SCOPE_IDENTITY() should be used instead. Check this article out for more details:
http://www.sqlteam.com/item.asp?ItemID=319

Tara Kizer
aka tduggan

Kristen
Test

22859 Posts

Posted - 2006-05-08 : 03:54:20

"IN ACTUAL CODES, I HAVE MORE THAN ONE STATEMENT TO PROCESS SO I WOULD LIKE TO BIND THEM TO PROCESS EITHER ALL OR NOTHING."

In that case you need to check for, and handle, any error after each statement:


BEGIN TRANSACTION
... operation one ...
IF @@ERROR <> 0 GOTO ErrorProcess
... operation two ...
IF @@ERROR <> 0 GOTO ErrorProcess
... repeat! ...
COMMIT
RETURN 0

ErrorProcess:
ROLLBACK
RETURN 1

Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-05-08 : 20:43:15

quote:
Originally posted by tkizer

@@IDENTITY can be inaccurate. SCOPE_IDENTITY() should be used instead. Check this article out for more details:
http://www.sqlteam.com/item.asp?ItemID=319

Tara Kizer
aka tduggan

see books online

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources