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 ca开发者_如何学Cn i call another SP or perform another MERGE on other tables in WHEN MATCHED block?
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'
The syntax shows that UPDATE SET or DELETE are the only options for merge_matched
as shown here
精彩评论