SQL Server 2008 - MERGE statement - Perform multiple actions in WHEN MATCHED block

Question

I am trying to use MERGE statement to accomplish the following. I have a SP and i am passing TableValue Parameter to it. This is how my SP looks like:

CREATE PROC sp_AddInformation
@IntoTbl dbo.Information  READONLY ,
@baseEventType dbo.EventType READONLY 

AS

BEGIN

MERGE Information
USING (SELECT InfoID, NewsID, NewsType FROM @IntoTbl ) AS baseInfo (InfoID, NewsID, NewsType)
ON (info.infoID = baseInfo.InfoID)
WHEN MATCHED
        THEN 
        --EXEC dbo.sp_insertEventInfo(@baseEventType) (This is not working)

        UPDATE set Info.Reporter = baseInfo.Reporter
WHEN NOT MATCHED BY SOURCE
        THEN 

        DELETE
WHEN NOT MATCHED BY TARGET
    THEN INSERT VALUES (InfoID, NewsID,NewsType);
END

Does anyone know how can i call another SP or perform another MERGE on other tables in WHEN MATCHED block?

Answer 1

It is not possible to call a stored procedure or merge from the when matched block. You are only allowed to do update or delete (or both). From the documentation on merge.

 <merge_matched>::=
     { UPDATE SET <set_clause> | DELETE }

You can use the output clause to capture the rows that was updated in when matched. The output can be captured in a table variable that you then can use in another merge statement or in a stored procedure. Use inserted.* and $action in the output. The rows from when matched is where $action = 'UPDATE'