No, MAX and such things might mislead you in production environments since there is also something called transaction. If your primary keys are
identities[
^], you can use
SCOPE_IDENTITY[
^] and
IDENT_CURRENT[
^] to get the latest identity generated on a table or in a scope. You probably will need the scope version, since the procedure execution is not atomic.
Read this short article:
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/[
^].
If your primary keys are not identity fields, than you will have to use locks and other fancy things - which I don't recommend.
In SQL Server 2012 there is at last implemented the
SEQUENCE[
^] object, which is part of the SQL standard for a very long time...