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.