{"id":181,"date":"2012-03-07T15:11:00","date_gmt":"1970-01-01T00:00:00","guid":{"rendered":"http:\/\/www.yazilimmutfagi.com\/?p=181"},"modified":"2012-03-07T15:11:00","modified_gmt":"1970-01-01T00:00:00","slug":"sql-server-hata-yakalama-mekanizmasi-try-catch","status":"publish","type":"post","link":"https:\/\/www.yazilimmutfagi.com\/index.php\/2012\/03\/07\/sql-server-hata-yakalama-mekanizmasi-try-catch\/","title":{"rendered":"SQL Server Hata Yakalama Mekanizmasi (TRY-CATCH)"},"content":{"rendered":"<h1>SQL Server Hata Yakalama Mekanizmasi (TRY-CATCH)<\/h1>\n<p>Bu makalemizde T-SQL de try catch ile hata yakalama mekanizmasinin temellerini anlamaya \u00e7alisacagiz. Programlama dillerinde oldugu gibi t-sql de de try catch bloklari olusan hatayi yakalamamizi saglar ve olusan hata hakkinda bilgi sahibi olmamiza imkan verir.<\/p>\n<h4>Hata yakalamak i\u00e7in TSQL&#8217;de asagidaki \u00f6rnek syntax yapisini kullaniriz.<\/h4>\n<pre class=\"brush:sql\">\nBEGIN TRY\n    SELECT a = 1\n    SELECT b = 1\/0\n    SELECT c = 3\nEND TRY\nBEGIN CATCH\n    PRINT 'Hata Mesaji'\nEND CATCH\n<\/pre>\n<p>Yukaridaki sorgu sonucu asagidaki gibi bir sonu\u00e7 d\u00f6ner<\/p>\n<pre class=\"brush:sql\">\na\n-----------\n1\n\n(1 row(s) affected)\n\nb\n-----------\n\n(0 row(s) affected)\n\nHata Mesaji\n<\/pre>\n<p>TRY CATCH kullanildiginda, bir hata olustugu zaman bu hata istemciye geri d\u00f6nd\u00fcr\u00fclmez, hata CATCH blogu i\u00e7inde islenir.<\/p>\n<a href='www.yazilimmutfagi.com'>.<\/a>\n<h3>Hata Fonksiyonlari<\/h3>\n\n<p>CATCH blogu i\u00e7inde kullanabilecegimiz ve olusan hata hakkinda bilgi alabilecegimiz bir \u00e7ok fonksiyon var.<\/p>\n\n<pre class=\"brush:sql\">\nBEGIN TRY\n\n    SELECT Sonuc = 5\/0\nEND TRY\nBEGIN CATCH\n    SELECT [Hata_Satiri] = ERROR_LINE(),\n           [Hata_Sayisi] = ERROR_NUMBER(),\n           [Hata_Onemi] = ERROR_SEVERITY(),\n           [Hata_Durumu] = ERROR_STATE()\n\n    SELECT [Hata_Mesaji] = ERROR_MESSAGE()\nEND CATCH\n<\/pre>\n<h4>Sonu\u00e7<\/h4>\n<pre class=\"brush:sql\">\n-----------\n\n(0 row(s) affected)\n\nHata_Satiri Hata_Sayisi Hata_Onemi  Hata_Durumu\n----------- ----------- ----------- -----------\n2           8134        16          1\n\n(1 row(s) affected)\n\nHata_Mesaji\n-----------------\nDivide by zero error encountered.\n\n(1 row(s) affected)\n\n<\/pre>\n<h4>CATCH blogunda kullanilan fonksiyonlar ve a\u00e7iklamalari su sekilde;<\/h4>\n<ul>\n<li>ERROR_NUMBER : Olusan hata numasi<\/li>\n<li>ERROR_MESSAGE : Olusan hataya ait hata mesaji.<\/li>\n<li>ERROR_LINE : Hatanin hangi satirda olustugunu g\u00f6sterir.<\/li>\n<li>ERROR_SEVERITY : Hatanin \u00f6nem d\u00fczeyini g\u00f6sterir. CATCH blogu \u00f6nem seviyesi 11 ve \u00fczerinde olan hatalari yakalar. 11 &#8211; 16 arasinda olan hatalar kullanici veya kod hatasidir. 17-25 arasinda olan hatalar ise yazilim ve donanimdan kaynaklanan hatalardir.<\/li>\n<li>ERROR_PROCEDURE : Eger hata bir stored procedure i\u00e7inde olustu ise onun adini d\u00f6nd\u00fcr\u00fcr.<\/li>\n<\/ul>\n<a href='www.yazilimmutfagi.com'>.<\/a>\n<h3>Stored Procedure i\u00e7inde olusan hatalari yakalamak<\/h3>\n<p>TRY CATCH blogu \u00e7agrilan bir stored procedure i\u00e7nide olusan hatayida yakalayabilir. Bir \u00f6rnek verecek olursak.<\/p>\n<pre class=\"brush:sql\">\nBEGIN TRY\n    EXEC Ornek_Procedure\nEND TRY\nBEGIN CATCH\n\tSELECT Hata_Satiri = ERROR_LINE(),\n\t\t   Procedure_Adi = ERROR_PROCEDURE()\n\tSELECT [Hata_Mesaji] = ERROR_MESSAGE()\nEND CATCH\n<\/pre>\n\n<h4>Sonu\u00e7<\/h4>\n<pre class=\"brush:sql\">\nHata_Satiri Procedure_Adi\n----------- -------------------\n3           NULL\n\n(1 row(s) affected)\n\nHata_Mesaji\n--------------------\nCould not find stored procedure 'Ornek_Procedure'.\n\n(1 row(s) affected)\n<\/pre>\n\n<h3>Transaction ile Hata Yakalama<\/h3>\n<p>Hata yakalama mekanizmasi Transaction islemlerinde de olduk\u00e7a faydalidir. Bu t\u00fcr islemleri asagidaki gibi yapabiliriz.<\/p>\n<pre class=\"brush:sql\">\n\nBEGIN TRY\n    BEGIN TRANSACTION\n       INSERT INTO dbo.DimAccount\n       (ParentAccountKey)\n        values('deneme')   \n\n    COMMIT TRANSACTION\nEND TRY\nBEGIN CATCH\n    if @@TRANCOUNT&gt;1ROLLBACK TRANSACTION\n    select Hata_Mesaji = ERROR_MESSAGE()\nEND CATCH\n<\/pre>\n<h4>Sonu\u00e7<\/h4>\n<pre class=\"brush:sql\">\nHata_Mesaji\n------------------------\nConversion failed when converting the varchar value \n'deneme' to data type int.\n\n(1 row(s) affected)\n\nMsg 3998, Level 16, State 1, Line 1\nUncommittable transaction is detected at the \nend of the batch. The transaction is rolled back.\n<\/pre>\n<a href='www.yazilimmutfagi.com'>.<\/a>","protected":false},"excerpt":{"rendered":"<p>Bu makalemizde T-SQL de try catch ile hata yakalama mekanizmasinin temellerini anlamaya &#231;alisacagiz. Programlama dillerinde oldugu gibi t-sql de de try catch bloklari olusan hatayi yakalamamizi saglar ve olusan hata hakkinda bilgi sahibi olmamiza imkan verir.<\/p>\n","protected":false},"author":517,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[422],"tags":[5152,5118,4367,4407],"_links":{"self":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/181"}],"collection":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/users\/517"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/comments?post=181"}],"version-history":[{"count":0,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/181\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/media?parent=181"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/categories?post=181"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/tags?post=181"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}