SQL return the insert row id

   1:  alter procedure [dbo].[usp_SomeName] (

   2:  @value varchar(100)

   3:  ) as

   4:  declare @id int

   5:  create table #out(id int, value varchar(100))

   6:  INSERT INTO test2

   7:  (value)

   8:  output inserted.* into #out

   9:  VALUES

  10:  (@value)

  11:  return (select top 1 id from #out)