从表中检索自动递增的列值，其中在单个存储过程中多次插入/选择

I have a stored procedure with multiple insert/select statements. Let's say I'm using the first insert to populate a "Manager" table. On insert, a ManagerId (incremented automatically) is added, but not referenced in the insert statement. I then wish to use the ManagerId from this table to insert a row into another table, where ManagerId is a foreign key. Sample code as follows..I have a stored procedure with multiple insert/