CODEWITHSUNDEEP
sql-server-2008merge
Asdfg
Asdfg

Reputation: 12203

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

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?

Upvotes: 11

Views: 11024

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

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'

Upvotes: 10

CPU_BUSY
CPU_BUSY

Reputation: 801

The syntax shows that UPDATE SET or DELETE are the only options for merge_matched

as shown here

Upvotes: 1

Related Questions