I thought I will share my experience how you can get identity value after insert using SQL Server. I have also written stored procedure that shows how to use one of the methods and return the value using sp argument so it can be used for other purposes.