How to use SCOPE_IDENTITY to retrieve the last ID that was inserted

Question

Suppose I have two table. First table's primary key is the foreign key for another table.

Table Member has its primary key as the foreign key in Member_detail.

So when I insert a row in Member table using a Stored Procedure, I need to get the primary key value to add to the Member_detail table.

One way I was using is :

SELECT Max(MemberID) 
FROM Member

Then passing this Memberid to my Member_detail table, but on the following post, I read that the Max function is not recommended and that I should use SCOPE_IDENTITY, but I don't know how to use of it.

Can anyone give me some example?

Answer 1

I've seen "funny" behavior with scope_identity. As a result, I like to use an output clause. Here's an example:

declare @id table (i int)
insert into Member (name) values ('NightKnight')
output (MemberId) into @id

select * from @id

Answer 2

SCOPE_IDENTITY returns the last identity value inserted into an identity column in the same scope.

Given you have 2 tables:

Member: id int (primaryKey), name varchar

Member_Detail: id int (primaryKey), pk int (foreignKey), name varchar

You can do this:

DECLARE @MemberId int

INSERT INTO Member (name) VALUES ('hello');

SET @MemberId = SCOPE_IDENTITY()

INSERT INTO Member_Detail (pk, name) VALUES (@MemberId, 'hello again')

MSDN Reference:

SCOPE_IDENTITY (Transact-SQL)

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.