sql server transaction syntax help

Hi,

I have an issue with my query.

1. I have 1 stored proc which have execution calls to multiple stored
procs within it.

2. I want to wrap that main stored proc in the transaction and rollback
if there are errors execution calls to other stored procs.  I don't
believe my code is accounting for errors occuring in the execution
statement to other stored proc.

3.  Is there an easy way to do this without creating tranaction on each
stored  proc and returning the error code?  How do I make this happen?
Below is the code.

Thanks

:D


ALTER           procedure spAG_Add_Product
@prodCost	money,
 @prodWeight	decimal,

 @prodDesc	nvarchar(1
00),
 @prodName	nvarchar(5
0),
 @prodSize	nvarchar(1
00),
 @pic_filename	nvarch
ar(50),
@userId		int,
@exhib_id	int
AS

declare @auth_Logic_id	int
declare @intErrorCode	int
BEGIN TRAN

SELECT    @auth_Logic_id= AG_Auth_Logic.Auth_Logic_ID FROM
AG_Auth_Logic
INNER JOIN   AG_Base_Active_State
 ON AG_Auth_Logic. Base_Active_State_ID

=  AG_Base_Active_State
. Base_Active_State_ID

WHERE     AG_Auth_Logic.Action_Description LIKE N'%category%'
AND  AG_Base_Active_State
.Is_Alive = 1

INSERT INTO  AG_Individual_Produc
t
(
Product_cost,
Product_weight,
 Product_description,

Product_name,
User_ID,
Auth_Logic_Id,
Product_size
)
VALUES (
@prodCost,
@prodWeight,
@prodDesc,
@prodName,
@userId,
@auth_Logic_id,
@prodSize
)



declare @prod_id	int
select  @prod_id = Scope_identity()

-- add to pic table
declare @pic_id_out	int
exec spAG_Add_Picture @pic_filename,
@prodName, @pic_id = @pic_id_out	output

declare @prod_pic_out	int
-- add to product_pic table
exec  spAG_Add_Product_Pic
ture @pic_id_out,
@prod_id, @prod_pic_id = @prod_pic_out output

-- add to  product_pic_in_exhib

exec  spAG_Add_Product_Pic
ture_in_Exhibition @prod_pic_out,
@exhib_id, @prod_id

select @prod_id

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
ROLLBACK TRAN
END

http://www.sommarskog.se/error-handling-II.html

Simon