در پست قبلی نحوه استفاده از بلوک Try-Catch برای مدیریت خطاها در SQL Server را بررسی کردیم. قبل از ادامه کار سناریویی را متسور شوید که شما به دستگاه ATM قصد برداشت یا واریز به کارت را دارید و در طی پروسه انجام اینکار، خطایی رخ می دهد. مثلا قصد برداشت دارید ولی دستگاه بنا به هر دلیلی هیچ پولی به شما پرداخت نمی کند پس نباید پولی هم از حساب شما کم شود. در طول این فرآیند اگر خطایی رخ دهد، باید وضعیت به حالت اول برگردد و نباید پروسه بدون خروجی درست، به اتمام برسد.


در هر سناریو transaction ، ما باید در مورد خطاها در اجرای کوئری های اس کیو ال، rollback transaction را اجرا کنیم. با استفاده از TRY-CATCH می توانیم خطا و استثنائات را که در یک stored procedure رخ داده است را گرفته کنیم و در بلوک CATCH می توانیم TRANSACTIONرا rollback کنیم و همه چیز به حالت قبل برگردد.در مثال زیر، در بلوک CATCH خطا و جزئیات آن گرفته شده و پس از ROLLBACK TRANSACTION اجرا می شود.


ابتدا یک SP ایجاد می کنیم:

Use WideWorldImporters

GO



/** Create Stored procedure **/

CREATE PROCEDURE mtb_SampleExceptionHndling

    @Error NVARCHAR(MAX) OUTPUT

AS

BEGIN



    BEGIN TRANSACTION;



    BEGIN TRY



        DELETE FROM Sales.Customers WHERE CustomerID = 1



    END TRY

    BEGIN CATCH



        SET @Error = 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + '; ' + Char(10) +

        'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) + '; ' + Char(10) +

        'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10)) + '; ' + Char(10) +

        'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10)) + '; ' + Char(10) +

        'Error Message: ' + ERROR_MESSAGE()



        IF @@TRANCOUNT > 0

            ROLLBACK TRANSACTION;

    END CATCH



    IF @@TRANCOUNT > 0

        COMMIT TRANSACTION;



END

GO

سپس SP ایجاد شده را Execute می کنیم.

DECLARE @ErrorMsg NVARCHAR(MAX)

EXEC mtb_SampleExceptionHndling @Error = @ErrorMsg output

Select @Err

خروجی اجرای SP فوق را در ذیل مشاهده می کنید:

/** Result **/

-----------



(0 rows affected)



-----------

Error Number: 547;

Error Severity: 16;

Error State: 0;

Error Line: 10;

Error Message: The DELETE statement conflicted with the REFERENCE constraint

"FK_Sales_CustomerTransactions_CustomerID_Sales_Customers".

The conflict occurred in database "WideWorld



(1 row affected)

SP به دلیل وجود خطا، اجرا نشده و وضعیت به حالت قبل از اجرای SP به دلیل وجود ROLLBACK TRANSACTION برگشت.