2015 international technet wiki summit 2015 saeid hasani structured error handling mechanism in sql...
TRANSCRIPT
International TechNet Wiki Summit2015
Saeid Hasani
Structured Error Handling Mechanism in SQL Server 2012 & 2014
2
Senior Database DeveloperSQL Server ConsultantFreelance SQL AuthorT-SQL TrainerTechnet and MSDN SQL Server Forums Moderator
Saeid Hasani
3
4
TRY• Try executing statements CATCH• Handle the errors if they occur
Is there any structured Error Handling mechanism in SQL Server?
5
Is there any structured Error Handling mechanism in SQL Server?
BEGIN TRY -- Start to try executing statements SELECT 1 / 0; /* Executing statements */END TRY -- End of trying to execute statementsBEGIN CATCH -- Start to Handle the error if occurs PRINT 'Error occurs!' /* Handle the error */END CATCH -- End of Handling the error if occurred
6
Is there any structured Error Handling mechanism in SQL Server?
7
Will all statements in TRY block try to execute?
BEGIN TRY -- Start to try executing statements PRINT 'Before Error!' -- Statement no1 SELECT 1 / 0; -- Statement no2 PRINT 'After Error!' -- Statement no3END TRY -- End of trying to execute statementsBEGIN CATCH -- Start to Handle the error if occurs PRINT 'Error occurs!' /* Handle the error */END CATCH -- End of Handling the error if occurred
8
Will all statements in TRY block try to execute?
9
Does the CATCH part automatically handle the
errors?
BEGIN TRY -- Start to try executing statements SELECT 1 / 0; -- StatementEND TRY -- End of trying to execute statementsBEGIN CATCH -- Start to Handle the error if occurs END CATCH -- End of Handling the error if occurred
Does the CATCH part automatically handle the
errors?
10
11
Does the CATCH part automatically handle the errors?
TRY• Try executing statements CATCH• Handle the error if occurs• RAISERROR
12
Does the CATCH part automatically handle the errors?
BEGIN TRY -- Start to try executing statements SELECT 1 / 0; -- StatementEND TRY -- End of trying to execute statementsBEGIN CATCH -- Start to Handle the error if occurs RAISERROR('Error!!!', 16, 1);END CATCH -- End of Handling the error if occurred
13
Does the CATCH part automatically handle the errors?
14
Does the CATCH part automatically handle the errors?TRY• Try executing statements CATCH• Handle the error if occurs• RAISERROR• ERROR_NUMBER()• ERROR_MESSAGE()• ERROR_SEVERITY()• ERROR_STATE()• ERROR_PROCEDURE()• ERROR_LINE()
15
Does the CATCH part automatically handle the errors?BEGIN TRY -- Start to try executing statements SELECT 1 / 0; -- StatementEND TRY -- End of trying to execute statementsBEGIN CATCH -- Start to Handle the error if occurs DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. );END CATCH -- End of Handling the error if occurred
16
Does the CATCH part automatically handle the errors?
17
Is it a good idea to use a general procedure as a
modular Error Handler routine?CREATE PROCEDURE spErrorHandlerASSET NOCOUNT ON; DECLARE @ErrorMessage NVARCHAR(4000);DECLARE @ErrorSeverity INT;DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. );
Is it a good idea to use a general procedure as a
modular Error Handler routine?CREATE PROCEDURE spTestASSET NOCOUNT ON;BEGIN TRY -- Start to try executing statements SELECT 1 / 0; -- StatementEND TRY -- End of trying to execute statementsBEGIN CATCH -- Start to Handle the error if occurs EXEC spErrorHandler;END CATCH -- End of Handling the error if occurredgo------------EXEC spTest
18
19
Is it a good idea to use a general procedure as a modular Error Handler routine?
20
Is it a good idea to use a general procedure as a modular Error Handler routine?CREATE PROCEDURE spTest AS SET NOCOUNT ON;BEGIN TRY -- Start to try executing statements SELECT 1 / 0; -- StatementEND TRY -- End of trying to execute statementsBEGIN CATCH -- Start to Handle the error if occurs DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH -- End of Handling the error if occurred
21
22
What are the benefits of THROW
when we have RAISERROR?
23
1. Correct line number of the error!
CREATE PROCEDURE spTest AS SET NOCOUNT ON;
BEGIN TRY SELECT 1/0END TRYBEGIN CATCH declare @msg nvarchar(2000) = error_message();
raiserror( @msg , 16, 1); THROW END CATCH
24
25
2. Easy to use
Another benefit of using the THROW statement is that there is no need for extra code in RAISERROR.
26
3. Real Terminator!
BEGIN CATCH DECLARE @msg NVARCHAR(2000) = ERROR_MESSAGE(); RAISERROR( @msg , 16, 1); ----------------------------- CREATE TABLE #Saeid (id INT) INSERT #Saeid VALUES ( 101 ); SELECT * FROM #Saeid; DROP TABLE #Saeid; ----------------------------- THROW PRINT 'This will never print!!!';END CATCH
27
28
4. Independence of sys.messages
CREATE PROC sptest AS SET NOCOUNT ON;BEGIN TRY SELECT 1/0END TRYBEGIN CATCH THROW 60000, 'This a custom message!', 1; END CATCH
29
4. Independence of sys.messages
30
I want to check a condition in the TRY block. How
can I control the flow of execution and raise the
error?CREATE PROC sptest AS SET NOCOUNT ON;BEGIN TRY THROW 60000, 'This a custom message!', 1; END TRYBEGIN CATCH THROW END CATCH
31
I want to check a condition in the TRY block. How can I control the flow of execution and raise the error?
32
Does the CATCH part automatically rollback the
statements within the TRY part?
CREATE PROC sptest AS SET NOCOUNT ON;BEGIN TRY CREATE TABLE dbo.Saeid --No1 ( id int ); SELECT 1/0 --No2END TRYBEGIN CATCH THROW END CATCHgo------------------------------------->
EXEC sptest;SELECT *FROM dbo.Saeid;
33
34
Can someone use TRANSACTION in the TRY/CATCH block?
CREATE PROC sptest AS SET NOCOUNT ON;BEGIN TRY SET XACT_ABORT ON; --set xact_abort option BEGIN TRAN --begin transaction CREATE TABLE dbo.Hasani ( id int ); SELECT 1/0 COMMIT TRAN --commit transactionEND TRYBEGIN CATCH IF @@TRANCOUNT > 0 --check if there are open transaction? ROLLBACK TRAN; --rollback transaction THROW END CATCH------------------------------------->EXEC sptest;
SELECT *FROM dbo.Saeid;
35
36
Can someone use TRANSACTION in the TRY/CATCH block?
TRY block• XACT_ABORT• Begin transaction• Statements to try
• Commit transactionCATCH block• Check @@TRANCOUNT and rollback all transactions• THROW
37
See Also
Related TechNet Wiki Articles:
Error Handling within Triggers Using T-SQLT-SQL: Error Handling for CHECK Constraints
BOL link: http://technet.microsoft.com/en-us/library/ms175976.aspx