sql server transaction syntax help

[dmalhotr2001]

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_Produc t
@prodCost money,
@prodWeight decimal,
@prodDesc nvarchar(100),
@prodName nvarchar(50),
@prodSize nvarchar(100),
@pic_filename nvarchar(50),
@userId int,
@exhib_id int
AS

declare @auth_Logic_id int
declare @intErrorCode int
BEGIN TRAN

SELECT @auth_Logic_id= AG_Auth_Logic.A uth_Logic_ID FROM
AG_Auth_Logic
INNER JOIN AG_Base_Active_ State ON AG_Auth_Logic.B ase_Active_Stat e_ID
= AG_Base_Active_ State.Base_Acti ve_State_ID
WHERE AG_Auth_Logic.A ction_Descripti on LIKE N'%category%'
AND AG_Base_Active_ State.Is_Alive = 1

INSERT INTO AG_Individual_P roduct
(
Product_cost,
Product_weight,
Product_descrip tion,
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_Pictur e @pic_filename,
@prodName, @pic_id = @pic_id_out output

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

-- add to product_pic_in_ exhib
exec spAG_Add_Produc t_Picture_in_Ex hibition @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

[Simon Hayes]

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

Simon