December 27, 2012 at 6:26 am
Hi all,
My friend ask me the following transaction problem. Consider the following structure of tables as
Table1(int)
Table2(int)
Table3(int)
Now consider the following problem.
CREATE procedure [dbo].[insert_SP]
AS
DECLARE @errorcode int
BEGIN TRAN t1
INSERT INTO TaBLE1 VALUES(2)
INSERT INTO TaBLE2 VALUES('A')
SELECT @errorcode = @@ERROR
PRINT @errorcode
IF(@errorcode<>0)
BEGIN
GOTO PROBLEM
END
INSERT INTO TaBLE3 VALUES(3)
COMMIT TRAN t1
PROBLEM:
IF(@errorcode<>0)
BEGIN
PRINT 'Unexpected error occured'
ROLLBACK TRAN t1
END
GO
Since i am trying to insert Character in Integer it would give me the error hence i would expect that the Statement 'Unexpected error occured' should execute. But that is not the case over here. I notice that transactions are rollback , but it should show the error message as 'Unexpected error occured', which is not happening. I would like to know why this error is occuring.
December 27, 2012 at 6:54 am
Looks like the conversion error is aborting the batch before the error handling is reached. Have you tried TRY...CATCH instead?
John
December 27, 2012 at 7:09 am
FIrst, you really need to read the first article I reference below in my signature block regarding asking for help. You may have provided your code (and I am saying your code because I am tired of people saying "a friend" when they post a question), but you did not provide anything else for people to really use in helping you.
Second, the procedure aborts with this error so it never gets to the error checking:
Msg 245, Level 16, State 1, Procedure insert_SP, Line 7
Conversion failed when converting the varchar value 'A' to data type int.
You should consider using a TRY CATCH block as in the following:
create table dbo.table1(aCol int);
create table dbo.table2(aCol int);
create table dbo.table3(aCol int);
go
CREATE procedure [dbo].[insert_SP]
AS
BEGIN
DECLARE @errorcode int
BEGIN TRAN t1
begin try
INSERT INTO dbo.table1 VALUES(2)
INSERT INTO dbo.table2 VALUES('A')
INSERT INTO dbo.table3 VALUES(3)
COMMIT TRAN t1
end try
begin catch
PRINT 'Unexpected error occured'
ROLLBACK TRAN t1
end catch
END
GO
exec dbo.insert_SP;
go
drop procedure dbo.insert_SP;
go
drop table dbo.table1;
drop table dbo.table2;
drop table dbo.table3;
go
Please notice how I setup the create statements for the tables and at the end dropped them. This helps those of us help you to keep our sandbox databases clean.
December 28, 2012 at 3:11 am
Perfectly described Lynn.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply