Home How to get the last identity value using sp_executesql in StoredProcedure?
Reply: 0

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

user1605
1#
user1605 Published in June 19, 2018, 6:20 pm

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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.354684 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO