try:
-------------------------------------------------------------------------
CREATE PROCEDURE sp_Order_UpdateOrderFormHeadByAffirm
@OrderFormHeadID int,
@AffirmPerson nvarchar(50)
AS
BEGIN TRANSACTION
DECLARE @OrderFormNo nvarchar(50), @FranchiserNo nvarchar(10), @TotalSum decimal(18,4)
--更新状态为确认
UPDATE
AD_U_HEAD_A_SSGL
SET
Tag = 1
WHERE
OrderFormHeadID = @OrderFormHeadID
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
------------------------------------------------------------------------------------------ --返回 订单管理(HEAD)的一些信息
SELECT
@OrderFormNo = OrderFormNo,
@FranchiserNo = FranchiserNo,
@TotalSum = TotalSum
FROM
AD_U_HEAD_A_SSGL
WHERE
OrderFormHeadID = @OrderFormHeadID
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
------------------------------------------------------------------------------------------ --更新 订单管理(DATA)
UPDATE
AD_U_DATA_A_SSGL
SET
Tag = 1,
AffirmPerson = @AffirmPerson,
AffirmDate = GETDATE()
WHERE
OrderFormNo = @OrderFormNo
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
------------------------------------------------------------------------------------------
--经销代理资信余额(MAIN)
EXEC sp_Order_UpdateCreditBalance @FranchiserNo, 0, 0, 0, @TotalSum, 0
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
------------------------------------------------------------------------------------------
--新增 订单确认日志(SLOG)
INSERT INTO
AD_U_SLOG_A_DDQR(OrderFormNo, GoodsNo, Quantity,
UnitPrice, ProductPackingNo,TotalQuantity, TotalSum, Rebate, FactSum)
SELECT
OrderFormNo, GoodsNo, Quantity, UnitPrice, ProductPackingNo,
TotalQuantity, TotalSum, Rebate, FactSum
FROM
AD_U_DATA_A_SSGL
WHERE
OrderFormNo = @OrderFormNo
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END COMMIT TRANSACTION
GO
-------------------------------------------------------------------------
CREATE PROCEDURE sp_Order_UpdateOrderFormHeadByAffirm
@OrderFormHeadID int,
@AffirmPerson nvarchar(50)
AS
BEGIN TRANSACTION
DECLARE @OrderFormNo nvarchar(50), @FranchiserNo nvarchar(10), @TotalSum decimal(18,4)
--更新状态为确认
UPDATE
AD_U_HEAD_A_SSGL
SET
Tag = 1
WHERE
OrderFormHeadID = @OrderFormHeadID
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
------------------------------------------------------------------------------------------ --返回 订单管理(HEAD)的一些信息
SELECT
@OrderFormNo = OrderFormNo,
@FranchiserNo = FranchiserNo,
@TotalSum = TotalSum
FROM
AD_U_HEAD_A_SSGL
WHERE
OrderFormHeadID = @OrderFormHeadID
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
------------------------------------------------------------------------------------------ --更新 订单管理(DATA)
UPDATE
AD_U_DATA_A_SSGL
SET
Tag = 1,
AffirmPerson = @AffirmPerson,
AffirmDate = GETDATE()
WHERE
OrderFormNo = @OrderFormNo
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
------------------------------------------------------------------------------------------
--经销代理资信余额(MAIN)
EXEC sp_Order_UpdateCreditBalance @FranchiserNo, 0, 0, 0, @TotalSum, 0
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
------------------------------------------------------------------------------------------
--新增 订单确认日志(SLOG)
INSERT INTO
AD_U_SLOG_A_DDQR(OrderFormNo, GoodsNo, Quantity,
UnitPrice, ProductPackingNo,TotalQuantity, TotalSum, Rebate, FactSum)
SELECT
OrderFormNo, GoodsNo, Quantity, UnitPrice, ProductPackingNo,
TotalQuantity, TotalSum, Rebate, FactSum
FROM
AD_U_DATA_A_SSGL
WHERE
OrderFormNo = @OrderFormNo
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END COMMIT TRANSACTION
GO
在其中一步出错时,整个事务都回滚
SET XACT_ABORT ON
听说用以上这个可以,是不是这样的?
@OrderFormHeadID int,
@AffirmPerson nvarchar(50)
AS
SET XACT_ABORT ON
BEGIN TRANSACTION
..............................
1 SET XACT_ABORT ON
2 IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END哪种比较好?
如果出错,我是要整个存储过程都回滚的。
http://www.mswebcast.com.cn/technet/alogin.aspx?id=down&tid=msft062805vxpmBE PREPARED! (讲解错误处理方式以及嵌套事务)
http://www.sqlservercentral.com/columnists/spopovski/beprepared.asp