title: How do I return the result of a stored procedure to a select statement that is being used to insert records
body: <p>I am writing a script to transform some data following a software update. I am introducing a new table and need to extract data from an existing table into this new table. I need to take an existing varchar(max) field from one table, create a parent child record based on it in another table and insert the identity of the new parent record in another table.</p>
  
  <p>So it kinda want to look like this</p>
  
  <pre><code>INSERT INTO NEWTABLE (val1, val2, val3) 
  SELECT 
      val1,
      val2,
      EXEC AStoredProc(val3) -- this obviosuly doesnt work
  FROM EXISTINGTABLE
  
  CREATE PROCEDURE AStoredProc
    @Value nvarchar(max),
    @NewID int OUTPUT
  
    DECLARE @ParentID int
  
    INSERT INTO AnotherTable ([Text], ParentNodeID)
    VALUES ('',NULL)
  
    SELECT @ParentID = SCOPE_IDENTITY()
  
    INSERT INTO AnotherTable  ([Text], ParentNodeID)
    VALUES (@Value,@ParentID)
  
    SELECT @ParentID
  
  END
  </code></pre>
  
  <p>In the above example val1 and val2 fields are just being migrated directly to the new table. val3 in the new table is an int and is being inserted from the result of AStoredProc which takes the varchar(max) val3 value of the Existing table creates a couple of records in another table and returns the new parent ID from them, which is then inserted as val3 in the new table.</p>
  
  <p>Without worrying about the whys of the design, how can I essentially run some insert stuff in a Stored Proc and have the result returned directly to the outer Select statement?</p>