| dmalhotr2001@yahoo.com 2005-05-24, 8:24 pm |
| 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
|