December 12, 2002 at 4:19 am
I have created the following script
***********************************
use pubs
GO
CREATE TABLE dbo.t1 (c1 int NOT NULL) ON PRIMARY
GO
ALTER TABLE dbo.t1 WITH NOCHECK ADD CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED (c1) ON PRIMARY
go
create proc usp_test
as
begin tran
insert into t1 values (1)
if @@error <> 0 goto Quitwithrollback
insert into t1 values (2)
if @@error <> 0 goto Quitwithrollback
insert into t2 values (1)
if @@error <> 0 goto Quitwithrollback
commit tran
goto Quitwithcommit
Quitwithrollback:
if @@trancount > 0 commit tran
Quitwithcommit:
go
exec usp_test
go
drop table t1
go
drop proc usp_test
go
***********************************
According to the @@error function it should rollback the transaction , but it does not go to the Quitwithrollback label. It seems that the procedure comes out after executing the third insert statement(due to PK) and comes out. It does not executes the next line of @@error <> 0 .
Can any1 tell me how to trap the error in multi insert statements and in transaction mode and rollbak the whole if any error.
Seems @@error does not work.
December 12, 2002 at 4:34 am
Yes! I think someone else could give you a more definitive answer, but it seems certain errors just stop processing, dependent on the error severity. There is no generic error handler like in VB procedures or in Oracle, which would be a useful feature.
Regards,
Andy Jones
.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply