CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008stored-proceduresmerge
Denis
Denis

Reputation: 12077

SQL Merge Statement using stored proc as source

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);

Upvotes: 6

Views: 7498

Answers (3)

n8wrl
n8wrl

Reputation: 19765

Sometimes I create functions or views that return what an sproc would, and then write the sproc to just call the view/function. That way I encapsulate logic, am able to use the query in joins, and leverage sproc features.

Upvotes: 0

mellamokb
mellamokb

Reputation: 56769

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 ...

Upvotes: 8

Remus Rusanu
Remus Rusanu

Reputation: 294237

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.

Upvotes: 3

Related Questions