>DECLARE @li_Index INTEGER
>
>INSERT INTO tab_MyTable
>   (Field_1,
>    Field_2)
>VALUES
>   (@Field_1,
>    @Field_2)
>
>SELECT @li_Index = @@IDENTITY
>
>Now the SQL variable @li_Index value is set to the identity value used to insert the record into the table tab_MyTable. In fact, the SQL global variable @@IDENTITY always contains the last inserted identity for the session (do not be afraid of concurrent accesses).

There's only one thing to be aware of. If tab_MyTable has an insert trigger that inserts a row into another table, say tab_YourTable, AND tab_YourTable also has a column with the identity property, @@IDENTITY will contain the identity value that was used by tab_YourTable, NOT tab_MyTable.

-Mike