Geek asks and answers

SQL Merge Statement Using a Stored Procedure as a Source

I want to do something like this, but it does not compile. My saved proc returns a table. Here is what I am trying to do - maybe someone can point out what I'm doing wrong, as this does not 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);
+3
source share
3 answers

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

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 ...
+5
source

INSERT ... EXEC. , #temp @table MERGE.

+3

Sometimes I create functions or views that return what was sproc and then write sproc to just call the view / function. This way I encapsulate the logic, I can use the request in the connections and use the sproc functions.

0
source

More articles:


All Articles