Is there a way to get the Identity of the record when using an insert
statement?
I have a procedure that inserts a record into a usage log that needs to be
updated when the user logs out. How can I get the identity of that record
after I execute the insert statement?

Ross

If you are using ado then when you commit the data to the server, if the
identity field is part of the column list , the value will be updated.

ie.

select * from sometable where 1=2.

This gives you an empty dataset , one column of which is the identity
column.

insert  .. or .. append

fieldbyname('field1').asstring := 'xxx'
fieldbyname('field2').asstring := 'yyy'
Dont put anything into fieldbyname('identity').asinteger

post ... or updatebatch

var1 := fieldbyname('identity').asinteger ...... it will be there ... magic
I think makes all this work.

Declare your stored procedure as following:

CREATE PROCEDURE
  Bla Bla Bla
AS INSERT INTO
  Bla Bla Bla
GO
RETURN @@IDENTITY

ADOCommand1.Execute;
ADOCommand1.Parameter[0].Value contains the IDENTITY value.

If you are using the procedure's Result for something else you can
declare an output variable and us it like this:

CREATE PROCEDURE
  Bla Bla Bla
AS INSERT INTO
  Bla Bla Bla
GO
SELECT @MyIdentityVar = @@IDENTITY
RETURN whatever

HTH
--
Vassil Nazarov
http://web.orbitel.bg/vassil/

You can have multiple sql statements in the same Command text separated by
semicolons.
Using an AdoDataset with command text like this you get the value in the
returned dataset

Insert into SomeTable
(field1,field2)
Values(:field1,:Field2);
Select @@Identity

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Brian Bushay TeamB <BBus...@Nmpls.com> wrote in message
news:gm5vlts2v5vjj83vfpk3hcgfkv9v5d3aoq@4ax.com...

With SQLServer 2000, it might be better to use SCOPE_IDENTITY() rather than
@@Identity, as this avoids problems with identity values being changed
inside triggers.

Cheers,
Andy Mackie.

SELECT @@Identity solves problem,
but be careful with it, because @@Identity contains LAST issued identity
value regardles of table.
That is if you've an INSERT trigger on your table that inserts some records
in another tables, SELECT @@Identity after INSERT statement will return
Identity of the last record inserted in the trigger.

probably you should consider using GUID column instead of Identity because
with GUIDs you can safely create values yourself using NewID() function, so
you'll know the value of your key before you'll insert a new record into the
table

Does this mean that if someone else inserts a record at the same time I
would get the Identity of thier record not mine. Or is it smart enough to
look at the current transaction?

Ross

No - @@Identity is specific to your session, so you will get your own last
identity value. The problem is if you insert into a table with an identity
column which has a trigger that also inserts into tables with identity
columns, as Konstantin explained. Use SCOPE_IDENTITY() to avoid these
problems. SCOPE_IDENTITY in Books On Line explains the differences.

Andy Mackie.

Thanks for the help.
Ross

That makes sense.
Thanks
Ross