Retrieving multiple @@identity values

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)

Retrieving multiple @@identity values

Author

Topic

mrzope
Starting Member

7 Posts

Posted - 2002-08-27 : 03:04:27

Hello all, I have a stored procedure that adds data to 1tbl and sets the id used for the relationship variable in another tbl and it works fine. However, when I added a second table into the mix, the second id variable doesn't get set. Here's the SQL maybe someone can help me out on this one?

ALTER PROCEDURE dbo.insert_address (
@address_l2 varchar(50), @rel_addr_type int,
@first_name varchar(50), @last_name varchar(50), @fl_shipTo int, @fl_billing int, @rel_cust_ID int,
@cust_email varchar(50), @password varchar(50), @timestamp varchar(50),
@number int, @rel_phone_num_type int,
@rel_country_ID varchar(50),
@addr_name varchar(50), @address varchar(50),
@city varchar(50), @customerID int, @rel_state_ID varchar(50),
@zip int, @autonum int OUTPUT) AS
BEGIN TRAN
INSERT INTO dbo.addresses
(address_l2, rel_addr_type, rel_country_ID,
addr_name, address, city, rel_state_ID, zip)
VALUES (@address_l2, @rel_addr_type, @rel_country_ID,
@addr_name, @address, @city, @rel_state_ID, @zip)
SET @autonum = @@identity COMMIT TRAN GO

BEGIN TRAN
INSERT INTO dbo.cust_data
(cust_email, password, time_stamp)
VALUES
(@cust_email, @password, @timestamp)
SET @customerID = @@identity COMMIT TRAN GO

BEGIN TRAN
INSERT INTO dbo.cust_addr_rel
( rel_addr_ID, first_name, last_name, fl_shipTo, fl_billing, rel_cust_ID )
VALUES
(@autonum, @first_name, @last_name, @fl_shipTo, @fl_billing, @customerID ) COMMIT TRAN GO

BEGIN TRAN
INSERT INTO dbo.cust_phone_num_rel (
number,
rel_phone_num_type
)
VALUES (@number, @rel_phone_num_type )
COMMIT TRAN GO

There is quite a bit of stuff. I hope it is readable. Also if someone has a better, more efficient suggestion I am all ears. Thanks

Scott B

I ate what?

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-08-27 : 04:15:03

First, you should not do the inserts in separate transactions. Get rid of COMMIT TRAN GO and put one COMMIT after the last insert.

Secondly you need to add some error handling. After each insert statement you should check if @@error is nonzero and if this is the case you should rollback the transaction and leave the procedure

i.e. like

declare @ec int, @autonum int
begin tran
insert ...
select @ec = @@error, @autonum = @@identity
if @ec <> 0 goto rollback_label
insert ...

commit tran
return 0 -- success
rollback_label:
rollback tran
return @ec -- failure

When you say "the second variable does not get set" do you mean

SET @customerID = @@identity

I don't see any reason why that should not work if the dbo.cust_data table has a column with identity property.

mrzope
Starting Member

7 Posts

Posted - 2002-08-27 : 12:08:11

OK cool, I will try out the change and post the result. Thanks LrsG!

I ate what?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-27 : 13:13:22

You have a go after the first insert which will terminate the batch so the following inserts will not be part of the SP.
Guess that's why you put a committed transaction round each insert.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mrzope
Starting Member

7 Posts

Posted - 2002-08-28 : 18:12:46

Here's what I ended up doing since I had to have the inserts in separate transactions due to the use of @@identity more than once.

ALTER PROCEDURE dbo.insert_address
@address_l2 varchar(50), @rel_addr_type int,
@rel_country_ID varchar(50), @addr_name varchar(50),
@address varchar(50), @city varchar(50),
@rel_state_ID varchar(50), @zip int,

@cust_email varchar(50), @password varchar(50), @timestamp varchar(50), @ec int, @customerID int, @autonum int,

@first_name varchar(50), @last_name varchar(50), @fl_shipTo int, @fl_billing int,
@number varchar(50), @rel_phone_num_type int

AS

BEGIN TRAN address
INSERT INTO dbo.addresses
(address_l2, rel_addr_type, rel_country_ID,
addr_name, address, city, rel_state_ID, zip)
VALUES
(@address_l2, @rel_addr_type, @rel_country_ID,
@addr_name, @address, @city, @rel_state_ID, @zip)
SET @autonum = @@identity
SELECT @ec = @@error
IF @ec <> 0 GOTO rollback_label -- roll back the transaction
SELECT @@TRANCOUNT 'Start address Transaction'

BEGIN TRAN cust_data
INSERT INTO dbo.customer_data
(cust_email, password, time_stamp)
VALUES
(@cust_email, @password, CURRENT_TIMESTAMP)
SET @customerID = @@identity
SELECT @ec = @@error
IF @ec <> 0 GOTO rollback_label -- roll back the transaction
SELECT @@TRANCOUNT 'Start customer data Transaction'

BEGIN TRAN cust_addr_rel
INSERT INTO dbo.cust_addr_rel
( rel_addr_ID, first_name, last_name, fl_shipTo, fl_billing, rel_cust_ID )
VALUES
(@autonum, @first_name, @last_name, @fl_shipTo, @fl_billing, @customerID )
SELECT @ec = @@error
IF @ec <> 0 GOTO rollback_label -- roll back the transaction
SELECT @@TRANCOUNT 'Start address relations Transaction'

BEGIN TRAN cust_phone_num
INSERT INTO dbo.cust_phone_num_rel (
rel_cust_ID, number, rel_phone_num_type
)
VALUES (@customerID, @number, @rel_phone_num_type )
SELECT @ec = @@error
IF @ec <> 0 GOTO rollback_label -- roll back the transaction
SELECT @@TRANCOUNT 'Start phone number Transaction'

COMMIT TRAN address
COMMIT TRAN cust_data
COMMIT TRAN cust_addr_rel
COMMIT TRAN cust_phone_num
return 0 -- success

rollback_label:
ROLLBACK TRAN
RETURN @ec -- failure

I ate what?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources