How can i retrieve an auto number in a SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How can i retrieve an auto number in a SP

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2004-12-02 : 15:17:15

Hello,
i am executing a couple of inserts in a stored proceedure being called from an insert.

However, i would like to know how to retrieve the last inserted auto number value in the same SP, so i could update another table in the same SP.

would it be possible to use the set @transctnID = scope_identity() in the SP ?

Or do i implement this with a trigger instead ?

afrika

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-02 : 15:51:58

Yes you would use SCOPE_IDENTITY() to get the value. So here's an example...assume Column1 in Table1 is the identity column.

DECLARE @ID int

INSERT INTO Table1 (Column2, Column3)
VALUES (1, 2)

SET @ID = SCOPE_IDENTITY()

INSERT INTO Table2 (Column1)
VALUES @ID

So we just inserted into Table2 with the value from SCOPE_IDENTIT()

HTH

Triggers really aren't related here.

Tara

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-04 : 15:35:02

What Tara says here is correct. I just wanted to add one caveat. The SCOPE_IDENTITY() technique works when inserting ONE row of data into the primary table. If you are potentially multiuple rows, you'll need a different approach.

HTH

Let X = {All sets s such that s is not an element of s}

(X element of X) ==> (X not element of X)

(X not element of X) ==> (X element of X)

(Bertrand Russell Paradox)

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2004-12-05 : 08:40:40

thanks all
Afrika

Hippi
Yak Posting Veteran

63 Posts

Posted - 2004-12-05 : 13:07:15

quote:
Originally posted by Bustaz Kool

What Tara says here is correct. I just wanted to add one caveat. The SCOPE_IDENTITY() technique works when inserting ONE row of data into the primary table. If you are potentially multiuple rows, you'll need a different approach.

HTH

Let X = {All sets s such that s is not an element of s}

(X element of X) ==> (X not element of X)

(X not element of X) ==> (X element of X)

(Bertrand Russell Paradox)

Hey folk,

Could you post your approach here? I really want to know.

Thanks a bunch.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-05 : 13:37:40

>>Could you post your approach here

create table t (  id int identity(0,1), val char(1))

insert into t 
select 'A' union all select 'B'
go

create proc dbo.up_t_insert
	@val char(1),
	@id int output

as
set nocount on

	insert t (val)
	select @val

	select @id = scope_identity()

return
go

select * from t

declare @id int
exec dbo.up_t_insert 'C', @id output
select @id as last_id

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-05 : 17:03:31

-- Set of new primary keys

SELECT MyKey
FROM inserted

HTH

=================================================================
It was a woman that drove me to drink... And I never had the decency to thank her.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources