I wonder if it is possible to insert a new row and select the result in the same command?
I have a table that keeps track of incrementing ID numbers (auto inc) and like to get a new ID at the same time.
I don't want to create a SP because it would be easier to maintain if I can keep it in the same statement, but like to run one statement only.

This does not work:
Select max(emID) From EmpId Where (Insert into empID (emKey) values (NewID()))

It gives "Incorrect syntax near the keyword 'Insert'"