Getting the value of a primary key from a merge

Question

I have a stored proc that has a merge .. into statement. I am trying to get the value of the primary key out of the stored proc.

 SET @NewCompanyID  = @CompanyID  
 ....
 merge company as c
 using ( select @CompanyID) as compAlt (company_id )
 on compAlt.company_id = c.company_id
 when matched 
 then 
    update set ....
 when not matched 
 then 
     insert ...    ;


  SET @NewCompanyID = @@identity; 

If the merge statement runs the update, i want to set @NewCompanyID to whatever value was passed into the stored proc (@CompanyID). If the insert statement was executed, then I want to pass the @@identity. How do I do this?

Answer 1

I added before merge into

 DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));

then

  OUTPUT $action INTO @SummaryOfChanges;

 SELECT @Change = Change
        FROM @SummaryOfChanges;     /* there is always one update or insert */

        IF @Change = 'INSERT'
        BEGIN
            ....
        END

This worked fine.