scope_identity() in Dynamic SQL

• kiranpchandran

  Ten Centuries

  Points: 1010

  More actions

  October 9, 2002 at 6:49 am

  #158843

  Hi all,

  I am using MS SQL Server 2000 as backend and VB6 as front end. In my database I have to use identity field in dynamic sql for some reasons. When I used scope_identity() for getting the last number generated from the same scope, it returned null. Please help me, it is urgent and I am working on a multi user project. If it is not possible, please tell me the remedy for this

  Thanks in advance

  What I did is:

  Table1

  A int identity(1,1)

  B varchar(30)

  declare @sql nvarchar(100)

  set @sql = 'insert into table1 (b) values(''test'')'

  exec sp_executesql @sql

  select scope_identity()

• K. Brian Kelley

  SSC Guru

  Points: 114642

  More actions

  October 9, 2002 at 10:04 am

  #438175

  Remember that when a dynamic SQL statement is executed, it is executed in a separate batch from the calling statement. This means it's also a different scope. Therefore SCOPE_IDENTITY() in the calling batch won't work. You can do something akin to:

  
  
  declare @sql nvarchar(100)
  
     
  
  set @sql = 'insert into table1 (b) values(''test''); SELECT SCOPE_IDENTITY()'
  
  exec sp_executesql @sql
  
  

  K. Brian Kelley

  http://www.truthsolutions.com/

  Author: Start to Finish Guide to SQL Server Performance Monitoring

  http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

  Edited by - bkelley on 10/09/2002 10:04:51 AM

  K. Brian Kelley
  @kbriankelley

• kiranpchandran

  Ten Centuries

  Points: 1010

  More actions

  October 9, 2002 at 10:55 am

  #438179

  Dear bkelley

  Thank you for help!..

  Mr. chrhedga also helped me, now i got two different method to solve the problem. That is using @@identity

  Any way Thanks a lot

  with lov

  Kiran