title: SQL get back the wrong @@identity
body: <p>I have this SQL Server stored procedure:</p>
  
  <pre><code>CREATE procedure [dbo].[sp_FinancialInstitutionsAdd]
      @Name varchar(50)
  AS
      set nocount on
  
      begin tran
          declare @retval int = 0
          declare @rmdID int
  
          execute @rmdID = dbo.sp_MetaDataRecsAdd   
  
          select @rmdID as rmdID
  
          if @@ERROR &lt;&gt; 0
          begin
              rollback tran
              return -1
          end
  
          insert FinancialInstitutions (Name, rmdID)
          values (@Name, @rmdID)
  
          if @@ERROR &lt;&gt; 0
          begin
              rollback tran
              return -1
          end
  
          return scope_identity()
          commit tran
  </code></pre>
  
  <p>I can only get back the ID of the inserted <code>MetaDataRecs</code> table but I want the ID of the <code>FinacialInstitutions</code> table. The procedure works fine when executed directly in SQL but not when executed with a <code>ExecuteScalar</code> command from code.                      </p>