The TRY…CATCH error control method has been introduced in SQL Server 2005 following similar exception handling construct in C++ and C# languages. A TRY…CATCH construct catches all execution errors with severity greater than 10 that will not result in the termination of the database connection. As such, it is very useful in verification of the database bound information. Error handling tasks can be handled in stored procedures which required client logic formerly.
But not every error is caught, even though SQL database developers may prefer that feature very much. Wrong object name errors are not caught, the control flow breaks altogether. Even Query Editor parser is not catching these errors. Syntax errors are also outside the domain of errors for TRY…CATCH. Syntax errors, on the other hand, are caught by Management Studio Query Editor parser.
There is an interesting technique for catching all errors: nest the TRY-CATCH stored procedure inside another TRY-CATCH stored procedure. The outer sproc will catch the uncaught errors by the inner sproc.
The following examples with results demonstrate what happens for different exception scenarios.
RAISERROR with severity 11-19 will jump to CATCH: Using RAISERROR
use tempdb;
-- Identity insert attempt flows to CATCH
begin try
insert into AdventureWorks2008.Production.Product (ProductID)
select 800
print 'Try passed OK'
end try
begin catch
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
end catch
/* CATCH
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
544 16 1 NULL 2 Cannot insert explicit value for identity column in table 'Product' when IDENTITY_INSERT is set to OFF.
*/
-- The following examples don't flow to CATCH
-- In some cases the ERROR is a compile error, other cases execution error
begin try
select HighestTicket=dateadd(UnitPrice)
from AdventureWorks2008.Sales.SalesOrderDetail
print 'Try passed OK'
end try
begin catch
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
end catch
/* NO CATCH ERROR
Msg 174, Level 15, State 1, Line 2
The dateadd function requires 3 argument(s).
*/
begin try
select HighestTicket=maxi(UnitPrice)
from AdventureWorks2008.Sales.SalesOrderDetail
print 'Try passed OK'
end try
begin catch
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
end catch
/* NO CATCH ERROR
Msg 195, Level 15, State 10, Line 2
'maxi' is not a recognized built-in function name.
*/
begin try
select HighestTicket=max(UnitPrice)
from AdventureWorks2008.Sales.SalesOrderDetailOMEGA
print 'Try passed OK'
end try
begin catch
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
end catch
/* NO CATCH ERROR Message
Msg 208, Level 16, State 1, Line 2
Invalid object name 'AdventureWorks2008.Sales.SalesOrderDetailOMEGA'.
*/
begin try
insert AdventureWorks2008.Production.Product (Name)
select values 'Rocky Mountain Bike'
print 'UPDATE succeeded'
end try
begin catch
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
end catch
/*
NO CATCH ERROR Message
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'values'.
*/
------------
Following is an interesting technique for catching all errors: nest the TRY-CATCH stored procedure ( sprocHighestTicketOrder) inside another TRY-CATCH stored procedure (sprocMAINHighestTicketOrder). The outer sproc will catch the uncaught errors by the inner sproc. We can test for where the error occured by looking at the ERROR_PROCEDURE() function (ErrorProcedure) return.
-- MSSQL script passes parse, however, it throws an execution error
-- T-SQL TRY - CATCH not catching
BEGIN TRY
DECLARE @HighestTicket money
SELECT @HighestTicket = MAX(UnitPrice)
FROM AdventureWorks2008.Sales.SalesOrderDetailx
PRINT 'Try passed OK'
END TRY
BEGIN CATCH
SELECT WhereAmI = 'scriptHighestTicketOrder',
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO
/* Msg 208, Level 16, State 1, Line 5
Invalid object name 'AdventureWorks2008.Sales.SalesOrderDetailx'.
*/
-- T-SQL erroneous stored procedure compiles OK
-- Internal TRY-CATCH will not catch
CREATE PROC sprocHighestTicketOrder @HighestTicket money OUTPUT
AS
BEGIN
BEGIN TRY
SELECT @HighestTicket = MAX(UnitPrice)
FROM AdventureWorks2008.Sales.SalesOrderDetailx
PRINT 'Try passed OK'
END TRY
BEGIN CATCH
SELECT WhereAmI = 'sprocHighestTicketOrder',
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
GO
-- T-SQL stored procedure without error
-- Outer sproc will catch error not caught in inner sproc
CREATE PROC sprocMAINHighestTicketOrder
AS
BEGIN
BEGIN TRY
DECLARE @HighestTicket money
EXEC sprocHighestTicketOrder @HighestTicket OUTPUT
PRINT @HighestTicket
PRINT 'Try passed OK'
END TRY
BEGIN CATCH
SELECT WhereAmI = 'sprocMAINHighestTicketOrder',
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
GO
EXEC sprocMAINHighestTicketOrder
GO
/* Results
WhereAmI ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
sprocMAINHighestTicketOrder 208 16 1 sprocHighestTicketOrder 6 Invalid object name 'AdventureWorks2008.Sales.SalesOrderDetailx'.
*/
------------
No comments:
Post a Comment