ibatis-user-cs mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nicholas Piasecki <nicho...@piasecki.name>
Subject Re: SELECT @@IDENTITY vs SELECT SCOPE_IDENTITY
Date Tue, 20 May 2008 12:23:06 GMT
Don't use <selectKey>. Embed SELECT SCOPE_IDENTITY() AS value at the  
bottom of your insert statement and use the returnClass.

<insert id="InsertSomething" parameterClass="Foo" resultClass="int">
     INSERT INTO Foos ( Name ) VALUES ( #Name# )
     SELECT SCOPE_IDENTITY() AS value
</insert>

I was involved in a long discussion about this before, but it has to  
do with the way iBATIS issues the <selectKey> query. It's sent in a  
separate batch, and MSSQL's SCOPE_IDENTITY() only works at the batch  
level.

It's not really a bug, just an obscure difference between the way  
similar functions for other databases work.

V/R,
Nicholas Piasecki

On May 20, 2008, at 3:07 AM, Alon Hirsch wrote:

> Hi,
>
> It seems that there is an issue using SELECT SCOPE_IDENTITY() in order
> to get the ID of an inserted record using SQL Server 2005.
> When using SCOPE_IDENTITY - the returned value is 0 instead of the
> actual value returned from the SQL, but when using @@IDENTITY, the key
> is returned.
>
> Is this a bug or by design ?
> Is there any change that the system can be made to work with
> SCOPE_IDENTITY as well ?
>
> Thanx,
> Alon


Mime
View raw message