Primary key value after insertion of row in SQL Server 2005

sql-server-2005 stored-procedures primary-key insert-update
Shailesh Sahu picture Shailesh Sahu · Jul 23, 2011 · Viewed 10k times · Source

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.

Answer

marc_s picture marc_s · Jul 23, 2011

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.

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More
Check if a string contains a substring in SQL Server 2005, using a stored procedure

I've a string, @mainString = 'CATCH ME IF YOU CAN'. I want to check whether the word ME is inside @mainString. How do I check if a string has a specific substring in SQL?

Read More