SQL Transaction Problem

[Kaushal Arora]

I have a procedure running with transaction used in it. I have used multiple insert in the procedures and finally return the scope_identity() of one of the insert by first setting a variable with scope_identity() and then returning it with select @variable at the end of procedure after commit and in case of RollBack i m returning it as select -9 The issue what am i facing is that sometimes it is skipping the return value in case of Commit Transaction as it is not returning anything to the code. I am using it like this:

Full Procedure Code with Insert Commands here

set @variable=scope_identity()

IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK

Select -9

END

COMMIT

select @variable

The procedure is not going in the @@ERROR section and also all the insert values are there in tables after commit but why sometimes it is not returning the value required. I am using ExecuteScaler in the code. Regards, Kaushal Arora Regards, Kaushal Arora

[Ashfield]

You are not setting your variable: set @variable=scope_identity() IF @@ERROR <> 0 BEGIN -- Rollback the transaction ROLLBACK Select **@variable =** -9 END COMMIT select @variable

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Kaushal Arora]

I am saying it is committing the records not giving any error and rollback. It is not returning the value in case of committing the records.

[Ashfield]

Without the rest of your code its kinda hard to say then.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP