14

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?

Community
  • 1
  • 1
NightKnight
  • 291
  • 1
  • 5
  • 14

2 Answers2

22

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.

Tanner
  • 22,205
  • 9
  • 65
  • 83
stian.net
  • 3,928
  • 4
  • 25
  • 38
  • SCOPE_IDENTITY returns the last identity values that are generated in any table in the current session. SCOPE_IDENTITY returns values inserted only within the current scope – stian.net Jan 24 '12 at 12:02
  • my next question might be silly. but please dont mind. if there are two users and both are doing insertion at the same time then for the each user scope_identity value will be different after insertion? for both there will be different session ? – NightKnight Jan 24 '12 at 12:06
  • It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function. – stian.net Jan 24 '12 at 12:18
4

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
Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • Be aware that if someone adds a trigger to the `Member` table, then the `OUTPUT` clause will give you an error: [The target table 'Member' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause](https://stackoverflow.com/q/13198476/12597) – Ian Boyd Jul 03 '23 at 19:26
  • @IanBoyd - that issue is specifically why I use an INTO clause in this answer. – Ben Thul Jul 03 '23 at 20:30