How to return last identity value generated for a specific table in the current session and the current scope

Question

I am inserting a row in Table1. I am inserting the last Srno(identity) of this Table1 and some other values in Table2 I am doing all this in one stored procedure

CREATE PROCEDURE proc_test1 
(
    @MultipleImgsTvp AS testdbtype READONLY,
    @ClaimDetailsTvp AS testdbtype1 READONLY
)
AS BEGIN

    INSERT INTO dbo.Tbl_ClaimDetails (BranchRemark, BrMkrid, BrMkrdt, BAZClaimNo, HORemark, HoMkrid, hoMkrdt, ClaimType, ContactDetails, VendorName)
    SELECT  BranchRemark
        ,   BrMkrid
        ,   GETDATE()
        ,   BAZClaimNo
        ,   HORemark
        ,   HoMkrid
        ,   GETDATE()
        ,   ClaimType
        ,   ContactDetails
        ,   VendorName
    FROM @ClaimDetailsTvp

    DECLARE @id INT = SCOPE_IDENTITY()

    DECLARE @ClaimNo VARCHAR(25)
    SET @ClaimNo = (
        SELECT Em_Branchcdnew
        FROM tbl_xyz
        WHERE Em_empid = (SELECT BrMkrid FROM @ClaimDetailsTvp)
    ) + '/' + LEFT(CONVERT(VARCHAR, GETDATE(), 111), 8) + CONVERT(VARCHAR, @ID)

    UPDATE Tbl_ClaimDetails
    SET ClaimNo = @ClaimNo
    WHERE Srno = @ID

    ----------  
    INSERT INTO Tbl_ClaimImages (img, id, imgname)
        SELECT  img
            ,   id
            ,   imgname
        FROM @MultipleImgsTvp
    ----------  

END

But i am always getting 0 in ID.

UPDATE

Tbl_ClaimDetails

SrNo(identity) | Remark | BrMkrdt | ...so on

i want to get the last inserted SrNo of Tbl_ClaimDetails

Please show us the schema of table Tbl_ClaimDetails, which column is the IDENTITY column? — Adriaan Stander, Sep 03 '13 at 08:05
this link may be helpful. using a different method to retrieve id could help. http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/ — xycf7, Sep 03 '13 at 08:07
i want to get the SrNo of last inserted row from Tbl_ClaimDetails and then insert rows to Tbl_ClaimImages with that SrNo — SamuraiJack, Sep 03 '13 at 08:34

score 3 · Accepted Answer · answered Sep 03 '13 at 08:42

3

Shouldn't your final insert statement be:

 INSERT INTO Tbl_ClaimImages (img, id, imgname)
        SELECT  img
            ,   @id
            ,   imgname
        FROM @MultipleImgsTvp

Raj

answered Sep 03 '13 at 08:42

Raj

10,653
2
45
52

+1 for catchy eye and marked as answer for correct answer. I probably would have spent centuries trying to figure out whats wrong. Thanks man. :) – SamuraiJack Sep 03 '13 at 09:06

score 1 · Answer 2 · answered Sep 03 '13 at 08:35

1

If I understand you correctly, you can use the OUTPUT section of the query like so :

INSERT INTO dbo.Tbl_ClaimDetails (BranchRemark, BrMkrid, BrMkrdt, BAZClaimNo, HORemark, HoMkrid, hoMkrdt, ClaimType, ContactDetails, VendorName)
 OUTPUT inserted.id INTO @ID
    SELECT  BranchRemark
        ,   BrMkrid
        ,   GETDATE()
        ,   BAZClaimNo
        ,   HORemark
        ,   HoMkrid
        ,   GETDATE()
        ,   ClaimType
        ,   ContactDetails
        ,   VendorName
    FROM @ClaimDetailsTvp

With the OUTPUT section, you can access the inserted row data by using inserted.column

answered Sep 03 '13 at 08:35

Pavel Ronin

576
4
11

I did not really understand.. `inserted.column`? Is `inserted` sql keyword or something? – SamuraiJack Sep 03 '13 at 10:03
Yes. In the OUTPUT section, the "inserted" functions similar to a table name. It also has intellisense. You can find more information/examples in this article http://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value – Pavel Ronin Sep 03 '13 at 13:32

How to return last identity value generated for a specific table in the current session and the current scope

2 Answers2