Return Newly Created Index As Value

Question

So far all of this code is working as it should, however, I have an additional step I can't seem to figure out. First of all, the INSERT command for the [CrossoverHits] table also contains an autonumber field [ID]. Now, what I need is to link another insert using the newly created index.

In other words, say the insert statement creates a record with ID of 1, PartNumber of '1234', today's date, some member, and a count of parts. I need the RETURN of this query to be 1 (equal to the ID) so that I may link this record to another table.

BEGIN TRANSACTION

    INSERT INTO [CrossoverHits] ([CrossoverHits].[PartNumber],
                                 [CrossoverHits].[InquiryDateTime],
                                 [CrossoverHits].[UserName],
                                 [CrossoverHits].[PartsFound])
    SELECT @PartNumber,
           GETDATE(),
           @CurrentMember,
           @PartsFound;

COMMIT TRANSACTION;

RETURN [ID]

possible duplicate of [Best way to get identity of inserted row?](http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row) — Clockwork-Muse, Mar 02 '14 at 00:35

score 1 · Accepted Answer · answered Sep 16 '13 at 19:27

1

You must use the SCOPE_IDENTITY.

answered Sep 16 '13 at 19:27

Hamlet Hakobyan

32,965
6
52
68

Return Newly Created Index As Value

1 Answers1