A question on @@IDENTITY

There is a stored procedure in our partner's application on MS SQL 2005 server like this(simplified):

Create Proc sp_SubmitData

{

@.data int

@.ID int OUTPUT

}

AS

BEGIN TRAN

INSERT INTO dataTable (data) VALUES(@.data)

-- Check @.@.ERROR

SET @.ID=@.@.IDENTITY

COMMIT TRAN

RETURN 0

In most cases @.ID returns the primay key of newly inserted row in dataTable. But in some cases (randomly, about 1/20 chances) @.ID returns a number we cannot figure out where it comes on the condition of a new row has been inserted into dataTable successfully. There is no trigger on dataTable. Is there any other chance that @.@.IDENTITY is refreshed by other sources between INSERT and SET sentence?

Chester

Try the SCOPE_IDENTITY function instead of @.@.identity; read the section that discusses both in books online. The short answer to your question can @.@.identity come from another source is "yes".|||

Thanks for your suggestion. Yesterday I browsed a lot on blogs and online books and have known the issue. But the problem is that there is a policy in our partner's company that the database is sealed. Only in this November can it be updated in the new roll out! It involves a lot of sites.

Chester

|||Unfortunately, @.@.identity has a checkered history. It has bitten many.