Primary key value after inserting a row in SQL Server 2005

Question

In SQL Server 2005, I insert a row into a table using a stored procedure, and I want to get the new primary key value right after inserting this row. I use the following approach to get the primary key value after the insert line

Create Proc Sp_Test @testEmail varchar(20)=null,-- Should be Unique @testName varchar(20)=null -- Should be Unique as begin insert into tableTest (testUserEmail,testUserName)values (@testValue,@testName) select MAX(ID) from tableTest --ID is Primary Key 

- or

 select ID from tableTest where testUserEmail =@testValue and testUserName = @testName 

- or

 select SCOPE_IDENTITY() as ID end 

Please suggest me which approach is best to accomplish the described task.

Answer 1

In any case - use SCOPE_IDENTITY() if the ID column is INT IDENTITY - only that will give you the correct results!

The first approach with MAX(ID) will fail if you have multiple clients inserting rows almost at the same time - you will get false results. Do not use this!

The third approach may fail if another entry with the same values ​​for E-Mail and name already exists.

Also, as a note: you should never use sp_ as your prefix! This is a prefix with Microsoft support and has performance flaws - use something else.

Answer 2

If you have an Identity column as a primary key, you should use SCOPE_IDENTITY()

You can also use the OPERATOR REPORT to return the identifier.

 insert into tableTest(testUserEmail,testUserName) output inserted.ID values (@testValue, @testName)