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
@prodCostmoney,
@prodWeightdecimal,
@prodDescnvarchar(100),
@prodNamenvarchar(50),
@prodSizenvarchar(100),
@pic_filenamenvarchar(50),
@userIdint,
@exhib_idint
AS

declare @auth_Logic_idint
declare @intErrorCodeint
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_Product
(
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_idint
select @prod_id = Scope_identity()

-- add to pic table
declare @pic_id_outint
exec spAG_Add_Picture @pic_filename,
@prodName, @pic_id = @pic_id_outoutput

declare @prod_pic_outint
-- add to product_pic table
exec spAG_Add_Product_Picture @pic_id_out,
@prod_id, @prod_pic_id = @prod_pic_out output

-- add to product_pic_in_exhib
exec spAG_Add_Product_Picture_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