title: INSERT return value of stored procedure
body: <p>I'm trying to <code>INSERT</code> a <code>RETURN</code> value for a stored procedure and I just can't get it to work.</p>
  
  <p>I have one table called <code>"person.person_ids"</code> with fields <code>"id int, uid uniqueidentifier"</code> </p>
  
  <p>and a bunch of other tables called for example <code>"person.employee"</code>, <code>"person.client"</code> etc. These tables all get their <code>"id"</code>s as foreign keys from the <code>"person.person_ids"</code> table.</p>
  
  <p>The procedure does this:</p>
  
  <pre><code>CREATE PROCEDURE PERSON.NewPerson
  AS 
  BEGIN 
      DECLARE @ret INT;
      INSERT INTO PERSON.ID_PERSON
      VALUES (NEWID());
      SET @ret = SCOPE_IDENTITY()
      RETURN @ret;
  END
  GO
  </code></pre>
  
  <p>and my <code>INSERT</code> looks like this:</p>
  
  <pre><code>    INSERT INTO PERSON.EMPLOYEE VALUES
      (EXECUTE PERSON.NewPerson, 1, '15434235', '10768348153', '1962-3-2', '1999-10-2', 'PETER', '', 'SMITH', 'HAMMER')
  GO
  </code></pre>
  
  <p>Basically I'm trying to create a stored procedure of function that I can call to automatically generate a new record in <code>"person.person_ids"</code> whenever I insert a new record into <code>"person.employee"</code>.</p>
  
  <p>I tried using <code>OUTPUT</code> from <code>INSERT</code> but I could not get it to work and INSERT isn't allowed inside of functions.</p>