3

In SQL Server 2005 I am inserting a row into a table using a stored procedure and I want to fetch the new primary key value just after inserting that row. I am using following approach to get primary key value after insertion row

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 better to perform described task.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shailesh Sahu
  • 5,801
  • 5
  • 18
  • 9

2 Answers2

5

By all means - use the SCOPE_IDENTITY() if your ID column is an INT IDENTITY - only that will give you the correct results!

The first approach with the MAX(ID) will fail terribly if you have multiple clients inserting rows almost at the same time - you'll get false results back. Don't use that!

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

Also, as a side-note: you should never use sp_ as your prefix! This is a Microsoft-reserved prefix and has downsides in terms of performance - use something else.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    Thanks marc_s fro reply...U told "The first approach with the MAX(ID) will fail terribly if you have multiple clients inserting rows almost at the same time - you'll get false results back. Don't use that!"......But i have 1 question in my mind that if multiple user inserting row than the set of sql statements will execute separately for each clients .until procedure end block – Shailesh Sahu Jul 23 '11 at 10:36
  • 2
    @Shailesh Sahu: **NO!** If you don't have any explicit transactions (and you don't), then the `INSERT` statement in itself is an implicit transaction. Once that's run, other clients can read the values - if you have hundreds of clients inserting simultaneously, you're guaranteed to get wrong results... and even if you have a transaction - others might be reading `WITH (NOLOCK)` or other mechanisms and get back false values.... – marc_s Jul 23 '11 at 10:47
2

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

You could also use the OUTPUT Clause to return the ID.

insert into tableTest(testUserEmail,testUserName) 
output inserted.ID
values (@testValue, @testName)
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281