December 6, 2007 at 10:42 pm
Never used Nested stored procs before. Below is my first experiment which blew up in my face. TestTable has one field -- an integer value. What is meaning of error message returned. What to do about it?
>>>>
CREATE PROC dbo.DivideByZero
AS
SET NOCOUNT OFF
BEGIN TRANSACTION
INSERT INTO TestTable(TestVal)
VALUES (456)
INSERT INTO TestTable(TestVal)
VALUES (1/0)
if @@ERROR <> 0
ROLLBACK TRANSACTION
else
COMMIT TRANSACTION
>>>>>>
CREATE PROC dbo.TestTransaction4
AS
SET NOCOUNT OFF
BEGIN TRANSACTION
INSERT INTO TestTable(TestVal)
VALUES (123)
exec dbo.DivideByZero
COMMIT TRANSACTION
exec dbo.TestTransaction4
>>>>>
Error message:
(1 row(s) affected)
Msg 8134, Level 16, State 1, Procedure DivideByZero, Line 10
Divide by zero error encountered.
The statement has been terminated.
Msg 266, Level 16, State 2, Procedure DivideByZero, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
Msg 3902, Level 16, State 1, Procedure TestTransaction4, Line 12
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
December 7, 2007 at 2:39 am
Msg 8134, Level 16, State 1, Procedure DivideByZero, Line 10
Divide by zero error encountered.
The statement has been terminated.
It says that you're dividing a number by zero, which can't be defined in any system. This will terminate the batch as the ANSI_WARNINGS set option is off by default.
Msg 266, Level 16, State 2, Procedure DivideByZero, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
Due to the termination of the batch, no rollback or commit has occured, which is why the engine raises the error.
--Ramesh
December 7, 2007 at 3:12 am
The problem here is that the ROLLBACK statement rolls back all the outstanding transactions. You can see this if you output @@TRANCOUNT in your sps. eg:
CREATE PROC dbo.DivideByZero
AS
SET NOCOUNT OFF
BEGIN TRANSACTION
PRINT 'TRANCOUNT 2.1: ' + CAST(@@TRANCOUNT AS VARCHAR)
INSERT INTO TestTable(TestVal)
VALUES (456)
INSERT INTO TestTable(TestVal)
VALUES (1/0)
IF @@ERROR <> 0 begin
ROLLBACK TRANSACTION
PRINT 'TRANCOUNT 2.2: ' + CAST(@@TRANCOUNT AS VARCHAR)
END ELSE BEGIN
COMMIT TRANSACTION
END
GO
CREATE PROC dbo.TestTransaction4
AS
SET NOCOUNT OFF
BEGIN TRANSACTION
INSERT INTO TestTable(TestVal)
VALUES (123)
PRINT 'TRANCOUNT 1.1: ' + CAST(@@TRANCOUNT AS VARCHAR)
EXEC dbo.DivideByZero
PRINT 'TRANCOUNT 1.2: ' + CAST(@@TRANCOUNT AS VARCHAR)
COMMIT TRANSACTION
GO
EXEC dbo.TestTransaction4
You should see that after the ROLLBACK in DivideByZero the tran count drops to zero.
What you do about this depends on what data you want inserted into the table at the end of the batch.
It may be sufficient to move the error handling to the calling proc like this:
ALTER PROC dbo.DivideByZero
AS
SET NOCOUNT OFF
PRINT 'TRANCOUNT 2.1: ' + CAST(@@TRANCOUNT AS VARCHAR)
INSERT INTO TestTable(TestVal)
VALUES (456)
INSERT INTO TestTable(TestVal)
VALUES (1/0)
GO
ALTER PROC dbo.TestTransaction4
AS
SET NOCOUNT OFF
BEGIN TRANSACTION
INSERT INTO TestTable(TestVal)
VALUES (123)
PRINT 'TRANCOUNT 1.1: ' + CAST(@@TRANCOUNT AS VARCHAR)
EXEC dbo.DivideByZero
IF @@ERROR <> 0 BEGIN
PRINT 'TRANCOUNT 1.2: ' + CAST(@@TRANCOUNT AS VARCHAR)
ROLLBACK TRANSACTION
PRINT 'TRANCOUNT 1.3: ' + CAST(@@TRANCOUNT AS VARCHAR)
END ELSE BEGIN
COMMIT TRANSACTION
END
GO
EXEC dbo.TestTransaction4
December 7, 2007 at 5:36 pm
Both responses were excellent and helpful. Thanks.
Bow wow!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply