May 8, 2008 at 9:32 am
Hello guys,
I have found a situation that I cannot explain. I was hoping that some of you understand this better.
Please look at the folowing script:
SET NOEXEC OFF
BEGIN TRAN
GO
CREATE TABLE test1(testint int, oooo)
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0
BEGIN
IF @@TRANCOUNT>0
ROLLBACK
INSERT INTO #error_status (has_error) VALUES (1)
SET NOEXEC ON
END
GO
DROP TABLE test1
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN
IF @@TRANCOUNT>0
ROLLBACK
INSERT INTO #error_status (has_error) VALUES (1)
SET NOEXEC ON
END
GO
IF @@TRANCOUNT>0
COMMIT
SET NOEXEC OFF
This script is supposed to stop at the first error but it doesn't. Instead if I comment the line (INSERT INTO #error_status (has_error) VALUES (1)) that generates another error, it stops after the first error.
Does anybody happen to know why is that?
Thanks
May 8, 2008 at 10:44 am
@@ERROR is resolved individually for each statement. So a bit of your code:
CREATE TABLE test1(testint int, oooo)
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0
BEGIN
As long as the GO works, you won't see an error. If you declare an error variable, @Err and then capture it like the following, I think you'll see it works:
DECLARE @Err int
CREATE TABLE test1(testint int, oooo)
SET @Err = @@ERROR
GO
IF @ERR <>0 OR @@TRANCOUNT=0
BEGIN
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply