Shirin Mataki na Mataki-Taba Don Kashe Gyara ... CATCH to Gudanarwa SQL Server Kurakurai

Gano kurakurai ba tare da kisa ba

TASKIYA ... CATCH bayani a cikin Transact- SQL gano da kuma shafar yanayin kuskure a cikin aikace-aikacen bayanai. Wannan sanarwa shine ginshiƙan kuskuren kuskuren SQL Server kuma yana da muhimmin ɓangare na ƙirar kayan aiki mai zurfi. TRY ... CATCH ya shafi SQL Server fara da 2008, Azure SQL Database, Azure SQL Data Hardware da kuma Daidaitan Data Kamfanin.

Gabatar da TRY..CATCH

TRY ... CATCH aiki ta ƙyale ka ka tantance kalmomi guda biyu na Transact-SQL: daya da kake son "gwada" kuma wani don amfani da "kama" duk wani kurakurai wanda zai iya tashi. A lokacin da Cibiyar Sadarwar SQL Server ta TRY ... CATCH bayani, nan da nan ya aiwatar da sanarwa da aka haɗa a cikin TRY rarraba. Idan bayanin TRY ya yi nasara, SQL Server tana motsawa kawai. Duk da haka, idan bayanin TRY ya haifar da kuskure, SQL Server yana aiwatar da bayanin CATCH don ɗauka kuskure a cikin alheri.

Rubutun mahimmanci yana ɗaukar wannan nau'i:

SAI SANYA {sql_statement | sanarwa_block} GASKIYA GAME DA CATCH [{sql_statement | sanarwa_block}] CATCH DAYA [; ]

TRY ... CATCH Misali

Yana da sauki don fahimtar amfani da wannan sanarwa ta hanyar amfani da misali. Yi tunanin cewa kai ne mai gudanar da bayanan albarkatu wanda ke dauke da tebur mai suna "Masu aiki," wanda ke dauke da bayani game da kowane ma'aikacin cikin kungiyarka. Wannan teburin yana amfani da lambar ID ma'aikaci mai mahimmanci a matsayin maɓallin farko . Kuna iya ƙoƙarin yin amfani da sanarwa da ke ƙasa don saka sabon ma'aikaci a cikin database ɗinku:

SANTA A cikin ma'aikatan (ID, sunaye na farko, last_name, tsawo) KASHI (12497, 'Mike', 'Chapple', 4201)

A karkashin yanayi na al'ada, wannan sanarwa zai ƙara jeri ga teburin ma'aikata. Duk da haka, idan ma'aikaci tare da ID 12497 ya rigaya ya kasance a cikin database, saka jeri zai karya mahimmancin maɓalli na farko kuma ya haifar da kuskuren da ke gaba:

Msg 2627, Level 14, Jihar 1, Lissafi 1 Rashin hankalin PRIMARY KYA KUMA 'PK_employee_id'. Ba za a iya saka maɓalli biyu a cikin 'dbo.employees' ba. An dakatar da sanarwa.

Duk da yake wannan kuskure ya ba ka da bayanin da kake bukata don warware matsala, akwai matsala biyu tare da shi. Na farko, saƙon ne cryptic. Ya haɗa da lambobin kuskure, lambobin layi da wasu bayanan da ba a fahimta ga mai amfani ba. Abu na biyu, kuma mafi mahimmanci, yana sa sanarwa ya ɓata kuma zai iya haifar da hadarin aikace-aikace.

Ƙaƙƙarwar ita ce ta kunsa sanarwa a cikin TRY ... CATCH bayani, kamar yadda aka nuna a kasa:

GASA WANNAN TAMBAYA A cikin ma'aikata (id, sunan farko, last_name, tsawo) KASHI (12497, 'Mike', 'Chapple', 4201) KASHE KASA KASA BUGA 'Kuskure:' + ERROR_MESSAGE (); EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mailing ma'aikaci', @recipients = 'hr@foo.com', @body = 'An sami kuskure ya haifar da sabon rikodin ma'aikaci.', @subject = 'Error Duplication Error'; CATCH

A cikin wannan misali, duk wani kurakuran da ke faruwa ya ruwaito ga mai amfani da aiwatar da umurnin da adireshin e-mail na hr@foo.com. Kuskuren da aka nuna wa mai amfani ya bayyana a kasa:

Kuskure: Rage PRIMARY KUMA KUMA 'PK_employee_id'. Ba za a iya saka maɓalli biyu a cikin 'dbo.employees' ba. Lissafi Mail.

Mafi mahimmanci, aiwatar da aikace-aikacen ci gaba da ci gaba, ƙyale mai shirye-shirye ya dauki kuskure a cikin sahihanci. Amfani da TRY ... CATCH bayanin shi ne hanya mai mahimmanci don ganowa da karɓar kurakurai da ke faruwa a aikace-aikace na SQL Server.

Ƙarin Ilimi

Idan kana so ka koyi game da Harshen Tambaya, karanta Gabatarwa ga SQL .