• انواع روش های به دام انداختن خطا (Error Trapping)
• @@error
• TRY…CATCH
• مفهوم سطح شدت خطا (Error Severity Level)
• استفاده از تراکنش در TRY…CATCH و برعکس
• خطاهایی که به دام نمی افتند

SQL Server بطور پیش فرض در صورت بروز خطا در داخل یک تراکنش، آن را بصورت اتوماتیک و ضمنی برای شما ROLLBACK نخواهد کرد. پس ناچاریم با به دام انداختن خطا ها کار Rollback و صدور پیغام مقتضی را خود به عهده بگیریم. بنابراین لازم است کمی با بحث Error Handling در T-SQL آشنا شوید.

در مقاله مدیریت تراکنش (بررسی ویژگی های تراکنش و تعریف آن) به ابتدایی ترین روش مدیریت خطا که مدتها بود برنامه نویسان SQL Server از آن استفاده می کردند (یعنی بررسی مقدار متغیر @@error) اشاره ای مختصر و در حد نیاز داشتیم. اما این شیوه به دلیلی که در ادامه به آن اشاره خواهد شد کارامد نیست.

یکی از مشکلاتی که در این شیوه مطرح است، مربوط به مدت زمان اعتبار مقدار متغیر @@error است. در واقع اعتبار مقدار این متغیر تا قبل از اجرای عبارت بعدی است. لذا اگر در مکانی از کد، احتمال بروز خطا متصور است، حتما از یک متغیر محلی و ثبت مقدار @@error و یا پردازش مستقیم خطای رخ داده استفاده کنید.

برای پی بردن به این واقعیت در اینجا دو مثال آورده ام.
مثال 1:
کدهای زیر را در یک پنجره Query اجرا کنید و نتیجه (قسمت Result) را مشاهده کنید. چه می بینید؟

1 SELECT 1/0

2 SELECT @@ERROR

3 SELECT @@ERROR

اولین دستور با شکست مواجه خواهد شد و پیغام خطایی مبنی بر تقسیم بر صفر (Divide by zero) صادر خواهد کرد. پس متغیر @@error حاوی شماره مربوط به خطا خواهد شد (که در اینجا شماره خطا 8134 است)

دومین دستور مقدار متغیر @@error را بر میگرداند (که همان عدد مذکور است) و سپس مقدار صفر به متغیر @@error اختصاص داده می شود (که به معنای اجرای موفقیت آمیز آخرین دستور است).

حالا سومین دستور مقدار 0 را به جای عدد 8134 برمیگرداند.
این موضوع محدود به اینگونه دستورات نمیشود. بر اساس مستندات Microsoft حتی عبارات شرطی مثل IF مقدار @@error را reset می کنند.

مثال 2:
1 SELECT 1/0

2 IF @@ERROR > 0 SELECT @@ERROR




اولین @@error آخرین شماره خطا را نگه داشته است که عددی بزرگتر از صفر است. پس شرط دستور IF صحیح است در نتیجه @@error انتخاب می شود، اما دومین @@error که بعد از بررسی دستور IF اجرا می شود بر خلاف تصور شماره خطا مربوط به آخرین دستور که شرط IF باشد را نگهداشته است که عدد صفر است. به بیان دیگر دستور IF مقدار @@error را reset کرده است.

نکته 1: لیست تمام پیغام های خطا در ویوی sys.messages قرار دارد. توجه داشته باشید که هر پیغام خطا به چند زبان در جدول وجود دارد. لذا تعداد سطرهای این جدول متناظر با تعداد کل خطاها نیست.

نکته 2: در مقاله قبل برای اینکه بررسی کنیم آیا خطایی رخ داده است یا خیر، مقدار متغیر @@error را با عملگر "نامساوی با صفر" مقایسه میکردیم. اما بر اساس داده های این جدول خطایی با شماره ی کمتر از 21 نداریم. در نتیجه بنظر میرسد عملگر "بزرگتر از صفر" کفایت کند. و از طرفی پیغام خطاهایی که کاربر تعریف می کند (user-defined) بواسطه ی sp_addmessage باید شماره ای بالاتر از پنجاه هزار داشته باشند.

پرسش از خواننده: آیا می توانید "نکته 2" را نقض کنید؟

سطح شدت خطا (Error Severity Level)
Error ها دارای پارامتری به نام Severity هستند که شدت خطا را تعیین می کند. خطاهایی با شدت 10 و کمتر از آن به عنوان هشدار (Warning) یا پیام های اطلاع راسانی در نظر گرفته می شوند. و Error هایی با سطح شدت 11 و بالاتر به عنوان Error محسوب می شوند (فرقی نمی کند که این پیغام با دستور raiserror تولید شده یا توسط دستور دیگر).
نکته ای که قصد بیان آن را داشتم این است که @@error شماره خطاهایی با شدت 10 و کمتر از آن را برنمیگرداند.

ساختار TRY…CATCH
می توان ادعا کرد که برای هر بخش از کد این امکان متصور است که در زمان اجرا به مشکلی پیش بینی نشده برخورد کرده و خطایی را تولید کند. در بیشتر مواقع این موضوع که بتوان راهکاری برای به دام انداختن این خطا پیدا کرد، از اهمیت ویژه ای برخوردار است. همانطور که مشاهده شد، برای آزمایش هر خط، از @@error استفاده کردیم که تعداد دستوراتمان را دو برابر می کند. واکنش بهتری نیز وجود دارد، به طوری که اگر یک مجموعه از عبارت ها، خطایی را در زمان اجرا تولید کرد، بتوان آنها را شناسایی و مهار نمود، اینجاست که ساختار TRY…CATCH مطرح می شود.


در T-SQL مشابه زبان برنامه نویسی Visual C# می توان Error ها را مورد پردازش قرار داد. این ساختار شامل دو بخش می باشد بلاک TRY و بلاک CATCH. زمانی که یک خطا در یکی از عبارات T-SQL ای که داخل بلاک TRY قرار دارد تشخیص داده شود کنترل پاس داده می شود به بلاک CATCH جایی که خطا پردازش می شود. شایان ذکر است که این ویژگی در نسخه 2005 به T-SQL افزوده شده است پس در نسخه ی 2000 به دنبال آن نگردید.

بعد از اینکه در بلاک CATCH خطا مدیریت شد کنترل منتقل می شود به اولین عبارت T-SQL که در ادامه ی عبارت END CATCH قرار دارد. در بلاک TRY عبارات T-SQL که بعد از عبارتی که موجب بروز خطا شد قرار دارند اجرا نخواهند شد.
اگر هیچ خطایی داخل بلاک TRY وجود نداشته باشد کنترل پاس داده می شود به عبارتی که بلافاصله بعد از دستور END CATCH وجود دارد (توضیحات تکمیلی را میتوانید از Book Online بدست آورید).

Syntax این ساختار:
1 BEGIN TRY

2 {T-SQL Statement}

3 END TRY

4 BEGIN CATCH

5 {T-SQL Statement}

6 END CATCH




همانند @@error این ساختار خطاهایی با شدت 10 و کمتر از آن را Handle نمی کند. به این معنا که اجرای دستورات داخل بلاک TRY ادامه پیدا میکنند و کنترل به بلاک CATCH منتقل نمی شود.

داخل محدوده ی بلاک CATCH توابع سیستمی زیر به جهت بدست آوردن اطلاعات پیرامون خطای رخ داده می توانند مورد استفاده قرار گیرند مثل شماره خطا، شدت خطا، پیام خطا و غیره.

1 ERROR_NUMBER(),

2 ERROR_SEVERITY(),

3 ERROR_STATE(),

4 ERROR_PROCEDURE(),

5 ERROR_LINE(),

6 ERROR_MESSAGE()




اولین تابع مشابه @@error شماره خطا را برمیگرداند.
چهارمین تابع از بالا، نام SP یا Trigger ای را که خطا در آن اتفاق افتاده است را برمیگرداند.
نکته: توابع فوق خارج از محدوده ی بلاک CATCH مقدار NULL را بر میگردانند.
متاسفانه تمام خطاها توسط این ساختار شناسایی نمی شوند. مثل خطای ارجاع معلق (Object Name Resolution). بطور نمونه میخواهیم داده های جدولی که وجود خارجی ندارد (قبلا ایجاد نشده است) را بازیابی کنیم.

1 begin try

2 select * from NonExistentTable

3 end try

4 begin catch

5 print 'error'

6 end catch




در اینجا کنترل به دست بلاک CATCH نمی افتد و پیغامی که مدنظر ما بود نمایش داده نمی شود.
حالا اگر همین دستور SELECT را داخل یک SP اجرا کنیم آنگاه توسط بلاک Catch مدیریت خواهد شد، به این صورت:
1 create procedure usp_example

2 as select * from NonExistentTable

3

4 begin try

5 execute usp_example

6 end try

7 begin catch

8 select ERROR_MESSAGE() as message_error

9 end catch




استفاده از TRY…CATCH در تراکنش
مثال 1:
مثال زیر نشان می دهد که چگونه بلاک TRY…CATCH داخل یک تراکنش کار می کند. عبارت داخل بلاک TRY خطای "نقض قید" تولید می کند.
01 USE AdventureWorks2012;

02 GO

03 BEGIN TRANSACTION;

04

05 BEGIN TRY

06 -- Generate a constraint violation error.

07 DELETE FROM Production.Product

08 WHERE ProductID = 980;

09 END TRY

10 BEGIN CATCH

11 SELECT

12 ERROR_NUMBER() AS ErrorNumber

13 ,ERROR_SEVERITY() AS ErrorSeverity

14 ,ERROR_STATE() AS ErrorState

15 ,ERROR_PROCEDURE() AS ErrorProcedure

16 ,ERROR_LINE() AS ErrorLine

17 ,ERROR_MESSAGE() AS ErrorMessage;

18

19 IF @@TRANCOUNT > 0

20 ROLLBACK TRANSACTION;

21 END CATCH;

22

23 IF @@TRANCOUNT > 0

24 COMMIT TRANSACTION;

25 GO

توضیح اجمالی اینکه در این مثال عبارت موجود در بلاک Try قیدی را نقض کرده در نتیجه کنترل به بلاک Catch منتقل می شود و در آنجا اطلاعاتی راجب خطای رخ داده صادر می شه و در صورتی که تراکنش فعالی وجود داشته باشه تراکنش Rollback می شه و کنترل اجرا برنامه به دستور بعد از END CATCH منتقل می شود و سپس برای اینکه تراکنشی که قبلا Rollback شده Commit نشود بررسی می کنیم که آیا تراکنش فعالی داریم یا خیر در صورتی که تراکنش فعالی داشتیم به این معناست که عبارات بدون خطا اجرا شدن پس تراکنش را Commit می کنیم.

مثال 2:
در این مثال تراکنش داخل بلاک TRY تعریف شده است.
پرسش از خواننده: چه لزومی دارد داخل بلاک Catch مقدار @@trancount بررسی شود؟ چرا که بنظر میرسد زمانی که کنترل به بلاک Catch منتقل شده است هنوز عمل commit صورت نگرفته است پس قطعا تراکنش فعال داریم.

01 BEGIN TRY

02 BEGIN TRANSACTION

03

04 INSERT INTO dbo.invoice_header

05 (invoice_number, client_number)

06 VALUES (2367, 19)

07

08 INSERT INTO dbo.invoice_detail

09 (invoice_number, line_number, part_number)

10 VALUES (2367, 1, 84367)

11

12 COMMIT TRANSACTION

13 END TRY

14 BEGIN CATCH

15 IF @@TRANCOUNT() > 0 ROLLBACK TRANSACTION

16 -- And do some cool error handling

17 END CATCH

خطاهایی که به دام نمی افتند:
خطاهایی هستند که شما آنها را نمی توانید بصورت سنتی (@@error) به دام بی اندازید. مثل Conversion Failed که زمانی رخ میدهد که سعی کنید مقدار رشته ای را در ستونی عددی درج کنید یا امثالهم.
این کد را امتحان کنید تا در عمل این موضوع را مشاهده کنید:

1 --Declaring a variable table

2 declare @t table(i int);

3

4 select 'statment befor error';

5

6 --Conversion failed

7 insert into @t values ('string_value');

8

9 select 'statement after error', @@ERROR;

توضیح مثال: ابتدا یک جدول (از نوع متغیری) ایجاد می کنیم تا به واسطه ی آن عمل درج داشته باشیم. سپس یک مقدار را انتخاب می کنیم (statement befor error) که در خروجی نیز ظاهر می شود (قسمت Result) سپس سعی میکنیم مقدار string_value را داخل ستون i که از نوع integer هست درج کنیم که با شکست مواجه می شود. که این شکست باعث break شدن batch نیز می شود در نتیجه دستورات بعدی موجود در این batch نیز اجرا نخواهد شد پس توسط تابع @@error نمی توانیم کد خطا را بدست آوریم.

البته می توانید کد فوق را به دو batch توسط GO تقسیم کنید. اما این ایده ی خوبی نیست. آیا میتوان داخل بدنه ی Stored Procedure از GO استفاده نمود؟
اما این مشکل در ساختار TRY…CATCH وجود ندارد. امتحان کنید:

01 --Declaring a variable table

02 declare @t table(i int);

03

04 begin try

05 --Conversion failed

06 insert into @t values ('string_value');

07 end try

08 begin catch

09 select @@ERROR as error_number

10 end catch

منبع :محمد سلیمآبادی/ آموزش SQL Server - نیک آموز