搜尋此網誌

2013年1月3日 星期四

Stored Procedure 的 Transaction 處理基本寫法


本範例: 欲刪除Option item 時, 也同時刪除 Option 所包含的下載附件.
----------------------------------------------------------------------------------------------------
CREATE PROC DeleteOption

@OptionId int

AS


BEGIN
DECLARE @TranStarted bit
SET @TranStarted = 0

DECLARE @ErrorCode int
SET @ErrorCode = 0

IF(@@TRANCOUNT = 0)
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END

DELETE FROM
DNNIT_MS_ProductOptionDownloads
WHERE
OptionId = @OptionId

IF(@@ERROR <> 0)
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END

    DELETE FROM
DNNIT_MS_ProductOptions
WHERE
OptionId = @OptionId

IF(@@ERROR <> 0)
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END

IF(@TranStarted = 1)
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END

RETURN 0

Cleanup:

IF(@TranStarted = 1)
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END

RETURN @ErrorCode

END

沒有留言:

張貼留言