Merge statement: Instead of DELETE, Enter record values in temporary table

I have the following Merge statement I'm working with:

    MERGE dbo.UnitOfMeasure AS target
    USING (
        SELECT * FROM dbo.UnitOfMeasure
    ) AS src
    ON 
        target.UOMId = src.UOMId
    WHEN NOT MATCHED THEN           
        INSERT 开发者_Python百科
            (
                UOMId,
                UOM
            )  
        VALUES 
            (
                src.UOMId,
                src.UOM
            )
    WHEN NOT MATCHED BY SOURCE THEN 
        DELETE    <-- Do something other than delete here
    WHEN MATCHED THEN
        UPDATE SET 
            target.UOM = src.UOM;

Instead of actually deleting the records, I'd like to run a stored procedure for each record that was going to be deleted. In essence, I can't simply delete these records. Instead, I have stored procedure that handles the deleting for me. I just need to pass in the unique ID (UOMId) to the stored proc.

Anyone know of way to do this using the Merge statement?

Thanks!

If I read the documentation correctly, it appears that the only options available to you from with the WHEN NOT MATCHED BY SOURCE clause are UPDATE and DELETE: http://technet.microsoft.com/en-us/library/bb510625.aspx.

Lame! Executing a procedure would be endlessly useful here! Even an INSERT would be sufficient for most scenarios...

Nevertheless, in your case, I would process these records in two steps. 1: Use MERGE with an UPDATE statement to flag the fields to be deleted. 2: Call your deletion code on the flagged fields.

Good luck!

For the purpose of your question, MERGE is merely an explicit delete statement, just like DELETE. I suppose, you do not call DELETE FROM table WHERE condition in cases where you need a special delete processing which you have put into a stored procedure. Same should apply to MERGE. Of course, as a language feature, it might be nice to have an ability to call SPs from MERGE. But MERGE is a set statement, like DELETE or UPDATE, and the fact that it uses these keywords doesn't mean that it actually calls the respective statements for every row individually.

So, if I were you, I would forget about calling procedures from MERGE. Instead, as an option, I would consider moving your stored procedure's logic to a trigger (either AFTER DELETE or INSTEAD OF DELETE, depends on the situation). Of course, that would mean I'd have to run a complex logic in a cursor inside a trigger, which most often is objectionable, but in some rare cases a necessary evil.