Running an INSERT statement a @variable without using EXEC

Running an INSERT statement a @variable without using EXEC

• Krijn

  Ok. It works fine.

  Thanks.

  Best Regards

  Raimund

  
  

• Jeffery Callahan

  You have to use dynamic SQL (which is what I presume you mean by using EXEC). I don't think there is any other way.
  

• HKJ

  You need to call the identity in the dynamic SQL

  declare @tablename varchar(100)

  declare @sql nvarchar(1000)

  declare @i int

  set @tablename = 'mytable'

  set @sql = 'insert into ' + @tablename + ' default values

  set @i = scope_identity()'

  exec sp_executesql @sql, N'@i int output', @i output

  select @i

  
  
  

• Chip Sands

  This does mean you can put this code in a UDF
  
  

• rustyoldboat

  If you put the Scope_identity call into the dynamic sql you will be able to capture it to a variable. That variable can then be returned if you use the sp_executesql form of dynamic sql.

  
  

• MaximusBrood

  If I use scope_identity with an insert-statement by dynamic SQL, scope_identity reurns NULL;
  
• Running an INSERT statement a @variable without using EXEC