How to get the last identity value using sp_executesql in StoredProcedure?

Question

I am inserting records using stored procedure and sp_executesql. Once I insert record using sp_executesql, i need the last inserted identity field value on that session.

ALTER proc [dbo].[spHoldTransaction] 


@RegisterNo int,
@StoreID int,
@Department varchar(50),
@TransactionDateFrom date,
@TransactionDateTo date,
@Comment Varchar(50)

AS
BEGIN

    DECLARE @RegisterID int;
    DECLARE @DatabaseName varchar(15);
    DECLARE @Batch int;


    SELECT @RegisterID=ID FROM Register WHERE Register.Number = @RegisterNo;

    SELECT @Batch = BatchNumber From Batch WHERE Status = 0 and RegisterID = @RegisterID


    SET @DatabaseName = 'xxx'

    SELECT  @Department=''''+REPLACE(@Department,',',''',''')+''''

    DECLARE @Qry nvarchar(MAX);

    DECLARE @ParamDefinition nvarchar(MAX);
    SET @ParamDefinition = N'@comment nvarchar(50),@StoreID int,@Batch int'

    SET @Qry = '
      INSERT INTO '+@DatabaseName+'.dbo.TransactionHold
     (
          [StoreID]       
          ,[HoldComment]          
          ,[BatchNumber]
          ,[ShippingNotes]
     )
     SELECT         
          @StoreID AS [StoreID]       
          ,@Comment AS [HoldComment]         
          ,@Batch AS [BatchNumber]
          ,'''' AS [ShippingNotes];       

     '

    EXECUTE sp_executesql @Qry, @ParamDefinition, @Comment, @StoreID, @Batch
    SELECT SCOPE_IDENTITY()

END

When I execute this above stored procedure, it's return empty. But TransactionHold has identity column Id

@AswaniMadhavan Yes I tried with `Scope_Identity()`, `Identity()`, `@@Identity`, and `Ident_Current()`. everything returns null — mohamed faisal, May 15 '18 at 06:50
@AswaniMadhavan can you show me your query, let me see. I tried now. Still it returns null — mohamed faisal, May 15 '18 at 06:58

score 0 · Accepted Answer · answered May 15 '18 at 07:04

0

Try retrieving the identity inside the same scope of the execute sql procedure and return the value as an OUT parameter. Do these changes:

SET @ParamDefinition = N'@comment nvarchar(50),@StoreID int,@Batch int, @identity int out'

SET @Qry = '
      INSERT INTO '+@DatabaseName+'.dbo.TransactionHold
     (
          [StoreID]       
          ,[HoldComment]          
          ,[BatchNumber]
          ,[ShippingNotes]
     )
     SELECT         
          @StoreID AS [StoreID]       
          ,@Comment AS [HoldComment]         
          ,@Batch AS [BatchNumber]
          ,'''' AS [ShippingNotes];       

     SET @identity = @@IDENTITY

     '

DECLARE @identity INT

EXECUTE sp_executesql @Qry, @ParamDefinition, @Comment, @StoreID, @Batch, @identity OUT

SELECT @identity

answered May 15 '18 at 07:04

EzLo

13,780
10
33
38

I copied your entire query and run it. still it return null, but the record is inserted. identity value is 46 in database – mohamed faisal May 15 '18 at 07:22
Do you have triggers linked to you inserting table? Use `SCOPE_IDENTITY()` instead of `@@IDENTITY`, also make sure that your table column is really an identity. As last resort you can try doing an `MERGE OUTPUT`. – EzLo May 15 '18 at 07:28
I confirmed that has `Id` is the column which is identity. I tried also Scope_Identity(). I will tell you one more thing. I am inserting into different server like `[192.xxx.xxx.xxx].DatabaseName.dbo.TableName`. That's why it is not returning? – mohamed faisal May 15 '18 at 07:36
@glennmaxwell yes, your SQL Server's session variables won't work throughout linked servers as it will create a different session on the remote server. Please read this post as how to retrieve the identity on a linked server https://stackoverflow.com/questions/5708996/best-way-to-get-identity-of-inserted-row-in-linked-server – EzLo May 15 '18 at 07:46
Yes, you are correct, I tried with local server. it is working with locally – mohamed faisal May 15 '18 at 07:57

How to get the last identity value using sp_executesql in StoredProcedure?

1 Answers1