SQL Merge Statement using stored proc as source

Question

I am looking to do something like this but it doesn't compile. My stored proc returns a table. Here's what I am trying to do - maybe someone can point to what I am doing wrong as this doesn't compile:

MERGE table AS target
   USING (EXEC [dbo].[sp_Something] @Rundate = '5/13/2011', @SPID = 56) 
      AS source (<Columns Returned By Stored Proc Go Here>)
ON TARGET.ID = SOURCE.ID 
WHEN MATCHED THEN
    UPDATE SET Field = Value...
WHEN NOT MATCHED THEN
    INSERT ( Field )
         VALUES (Value);

Answer 1

A stored procedure cannot be used where tables are expected. You must either use a table variable, subquery or a table-valued function. For example (not sure if this is valid, I've never used MERGE before):

DECLARE @Something TABLE (columns go here...)

INSERT @Something
EXEC [dbo].[sp_Something] @Rundate = '5/13/2011', $SPID = 56

MERGE table as target
    USING @Something
       AS Source ...

Answer 2

You can only to INSERT ... EXEC. The workaround is to spool into a #temp table or a @table variable and use that for the MERGE.