Scope_Identity in Stored Procedure

Question

I have stored procedure which has 3 insert statements. What I need is after each insert I want to know the inserted value of the ID by querying Scope_Identity.

Something like following :

insert into t1(name)values("david")
set @v1=Scope_Identity()

insert into t2(name)values("david2")
set @v2=Scope_Identity()

insert into t3(name)values("david3")
set @v4=Scope_Identity()

Is there any way to do that?

Have you tried it? Did it work? If not, in what way didn't it work? — Damien_The_Unbeliever, Sep 04 '13 at 06:38
http://stackoverflow.com/questions/481395/t-sql-identity-scope-identity-output-and-other-methods-of-retrieving-last — David Brabant, Sep 04 '13 at 06:39
Well, you've not shown enough for us to diagnose the issue, just from the above. The above code should work and `@v1`, `@v2` and `@v4` should contain values. (Not sure why `@v3` never appeared :-)) — Damien_The_Unbeliever, Sep 04 '13 at 06:42
[Looks like it is working here](http://sqlfiddle.com/#!6/d8aeb/1) — Mikael Eriksson, Sep 04 '13 at 06:43
@franchescototti Are you sure the tables have `identity` column ? — Robert, Sep 04 '13 at 06:44

davmos · Accepted Answer · 2013-09-04T07:18:55.470

3

CREATE TABLE t1 (id int identity, name varchar(30))
CREATE TABLE t2 (id int identity, name varchar(30))

DECLARE @v1 int, @v2 int

INSERT t1 (name) VALUES ('david')
SET @v1 = Scope_Identity()

INSERT t2 (name) VALUES ('david2')
SET @v2 = Scope_Identity()

SELECT @v1, @v2

Click here to see it in action at SQL Fiddle.

edited Sep 04 '13 at 07:18

answered Sep 04 '13 at 06:45

davmos

8,924
4
39
42

score 1 · Answer 2 · answered Sep 04 '13 at 06:49

1

Try this one -

DECLARE @temp TABLE
(
      id INT IDENTITY(1,1) PRIMARY KEY
    , name VARCHAR(20)
)

INSERT INTO @temp (name)
OUTPUT INSERTED.id
VALUES ('test1'), ('test2')

answered Sep 04 '13 at 06:49

Devart

115,199
22
161
180

Scope_Identity in Stored Procedure

2 Answers2