title: Stored Procedure as Parameter in MERGE [INSERT] Statement
body: <p>I need to merge two tables in following way:</p>
  
  <ul>
  <li>Target has one extra Column ID. This Id is coming FROM another Single Column Master Table. </li>
  <li>While Inserting the Record in Merge Statement I need to INSERT a new row into mater table and use its <code>id</code> to insert into TARGET table. </li>
  </ul>
  
  <p>I have created a Stored Procedure that Inserts and returns newly inserted ID. Now the Problem is inside SQL Merge, we can't call a stored Proc. </p>
  
  <p>What could be the solution of this issue? Cant use Scalar functions as INSERT can't be performed in Functions.</p>
  
  <pre><code>DECLARE @temp INT
  MERGE dbo.mytabletarget T
  USING dbo.mytableSource S
  ON T.refId=S.RefId
  WHEN MATCHED  THEN
  UPDATE 
  SET T.col1=S.col1,
      T.Col2=S.Col2
  WHEN NOT MATCHED BY TARGET THEN
  INSERT (Id,col1,col2)
  VALUES({Here i need value from SP. SP simply Inserts a new Id into master table and Returns it},S.col1,S.col2);
  GO
  </code></pre>