What's wrong with @@identity?  
Author Message
lluscious





PostPosted: Sat Jun 16 13:53:16 CDT 2007 Top

SQL Server >> What's wrong with @@identity?

The stored procedure below works just fine but I have been advised by some

SCOPE_IDENTITY(). That sometimes the wrong identity will be returned. Is
this true? It works fine.
Thanks,
T

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[IOrder]
(
@FranchiseID nvarchar(16),
@PrimaryVendorID int,
@Status nvarchar(15),
@Da****tered datetime,
@Customer nvarchar(50),



)
AS
SET NOCOUNT OFF;
begin tran
INSERT INTO [Order] ([FranchiseID], [PrimaryVendorID], [Status],
[Da****tered], [Customer],[DesignDate], [DesignAndVersion])



commit tran

SQL Server263  
 
 
Vt





PostPosted: Sat Jun 16 13:53:16 CDT 2007 Top

SQL Server >> What's wrong with @@identity? hi

check the following links

http://sqljunkies.com/Article/92CC4817-604D-4344-8BE0-4490F8ED24B6.scuk
http://davidhayden.com/blog/dave/archive/2006/01/17/2736.aspx

Regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com



> The stored procedure below works just fine but I have been advised by some

> SCOPE_IDENTITY(). That sometimes the wrong identity will be returned. Is
> this true? It works fine.
> Thanks,
> T
>
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
>
> ALTER PROCEDURE [dbo].[IOrder]
> (








> )
> AS
> SET NOCOUNT OFF;
> begin tran
> INSERT INTO [Order] ([FranchiseID], [PrimaryVendorID], [Status],
> [Da****tered], [Customer],[DesignDate], [DesignAndVersion])



> commit tran
>


 
 
Aaron





PostPosted: Sat Jun 16 14:05:04 CDT 2007 Top

SQL Server >> What's wrong with @@identity? > The stored procedure below works just fine but I have been advised by some

> SCOPE_IDENTITY(). That sometimes the wrong identity will be returned. Is
> this true? It works fine.


fine now, but tomorrow someone may add a trigger to the table that inserts
into another table with an IDENTITY column. When that happens, you'll get
the wrong value. SCOPE_IDENTITY() ensures that anything that happens
outside of the current scope (hence the name), including triggers, will be
ignored.

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006