SCOPE_IDENTITY

• Mauro Ciaccio

  SSCrazy

  Points: 2617

  More actions

  September 7, 2004 at 6:05 am

  #63423

  Hi,

  I'm struggling with the use of SCOPE_IDENTITY in a dynamic SQL statement. I have looked at the following approach

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=7311

  but what I actually need is to assign the SCOPE_IDENTITY to a variable. I have tried the following, but it does not return anything. This has got to be something really stupid but I'd appreciate any suggestions.

  declare @sql nvarchar(100)

  declare @NewAcctID int

    

  set @sql = 'insert into table1 (b) values(''test''); SELECT @NewAcctID = SCOPE_IDENTITY()'

  exec sp_executesql @sql, N'@NewAcctID int', @NewAcctID

  Thanks,

  Mauro

• Adam Machanic

  SSCoach

  Points: 15370

  More actions

  September 7, 2004 at 7:39 am

  #521956

  Mauro --

  You need to use sp_executesql's OUTPUT parameter:

  exec sp_executesql @sql, N'@NewAcctID int OUTPUT', @NewAcctID OUTPUT

  --
  Adam Machanic
  whoisactive

• Mauro Ciaccio

  SSCrazy

  Points: 2617

  More actions

  September 7, 2004 at 8:14 am

  #521967

  Didn't I say it was something simple. I'd tried the following (which I'd never used before) and failed

  exec sp_executesql @sql, N'@NewAcctID int', @NewAcctID OUTPUT

  I've also just realised this is a very simple neat way to return a limited number of parameters from sp_executesql (instead of using temp tables)

  Thanks very much for your help!