Return value after INSERT in sql server

Use SCOPE_IDENTITY() to get the new ID value

INSERT INTO table (name) VALUES('bob');

SELECT SCOPE_IDENTITY()