programming4us
programming4us
DATABASE

SQL Server 2008 : Transact-SQL Programming - TRY...CATCH Logic for Error Handling

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
6/16/2011 3:18:36 PM
SQL Server 2005 also introduced the TRY...CATCH construct, which you can use within T-SQL code to provide a more graceful mechanism for exception handling than was available in previous versions of SQL Server. In versions prior to 2005, error handling was typically done by checking @@ERROR after each SQL statement and often using the GOTO statement to branch to an error-handling routine.

A TRY...CATCH construct consists of two parts: a TRY block and CATCH block. When an error condition is detected in a T-SQL statement that is inside a TRY block, control is immediately passed to the CATCH block, where the error is processed. T-SQL statements in the TRY block that follow the statement that generated the error are not executed.

If an error occurs and processing is passed to the CATCH block, after the statements in the CATCH block are executed, control is transferred to the first T-SQL statement that follows the END CATCH statement. If there are no errors inside the TRY block, control is passed to the statement immediately after the associated END CATCH statement, essentially skipping over the statements in the CATCH block.

A TRY block is initiated with the BEGIN TRY statement and ended with the END TRY statement and can consist of one or more Transact-SQL statements between the BEGIN TRY and END TRY statements. The TRY block must be followed immediately by a CATCH block. A CATCH block is indicated with the BEGIN CATCH statement and ended with the END CATCH statement and can consist of one or more SQL statements. In SQL Server, each TRY block can be associated with only one CATCH block.

The syntax of the TRY...CATCH construct is as follows:

BEGIN TRY
one_or_more_sql_statements
END TRY
BEGIN CATCH
one_or_more_sql_statements
END CATCH

In a CATCH block, you can use the following error functions to capture information about the error that invoked the CATCH block:

  • ERROR_NUMBER()— Returns the error number

  • ERROR_MESSAGE()— Returns the complete text of the error message

  • ERROR_SEVERITY()— Returns the error severity

  • ERROR_STATE()— Returns the error state number

  • ERROR_LINE()— Returns the line number inside the procedure that caused the error

  • ERROR_PROCEDURE()— Returns the name of the stored procedure or trigger where the error occurred

Unlike @@ERROR, which is reset by each statement that is executed, the error information retrieved by the error functions remains constant anywhere within the scope of the CATCH block of a TRY...CATCH construct. Error functions can also be referenced from within a stored procedure invoked within a CATCH block. This allows you to modularize the error handling into a single stored procedure so you do not have to repeat the error-handling code in every CATCH block. Listing 1 shows an example of an error-handling procedure that you can use in your CATCH blocks.

Listing 1. An Example of a Standard Error Handler Procedure
go
create proc dbo.error_handler
as
begin
Declare @errnum int,
@severity int,
@errstate int,
@proc nvarchar(126),
@line int,
@message nvarchar(4000)
-- capture the error information that caused the CATCH block to be invoked
SELECT @errnum = ERROR_NUMBER(),
@severity = ERROR_SEVERITY(),
@errstate = ERROR_STATE(),
@proc = ERROR_PROCEDURE(),
@line = ERROR_LINE(),
@message = ERROR_MESSAGE()
-- raise an error message with information on the error
RAISERROR ('Failed to add new publisher for the following reason:
Error: %d, Severity: %d, State: %d, in proc %s at line %d, Message: "%s"',
16, 1, @errnum, @severity, @errstate, @proc, @line, @message)
Return
end



Listing 2 provides an example of the use of the TRY...CATCH construct in a T-SQL batch. Note that this CATCH block uses the dbo.error_handler procedure defined in Listing 1.

Listing 2. Using a TRY...CATCH Construct for Error Handling in a T-SQL Batch
use bigpubs2008
go
BEGIN TRY
INSERT INTO bigpubs2008.dbo.publishers
(pub_id, pub_name, city, state, country)
VALUES('9950', 'Sams Publishing', 'Indianapolis', 'IN', 'USA')
-- if no error occurs, we should see this print statement
print 'New Publisher added'
END TRY
BEGIN CATCH
-- invoke the error_handler procedure
exec error_handler
-- return a non-zero status code
END CATCH
-- if successful execution, return 0
go
Msg 50000, Level 16, State 1, Procedure error_handler, Line 18
Failed to add new publisher for the following reason:
Error: 2627, Severity: 14, State: 1, in proc (null) at line 2,
Message: "Violation of PRIMARY KEY constraint 'UPKCL_pubind'.
Cannot insert duplicate key in object 'dbo.publishers'."

If you want to capture and handle any errors that may occur within a CATCH block, you can incorporate another TRY...CATCH block within the CATCH block.

Note

Some errors with severity 20 or higher that would cause SQL Server to close the user connection cannot be handled by the TRY...CATCH construct. However, severity level 20 or higher errors that do not result in the connection being closed can be captured and handled by the CATCH block. Any errors with a severity level of 10 or less are considered only warnings or informational messages and not really errors, and thus they are not handled by the TRY...CATCH construct. Also, any compile errors (such as syntax errors) or object name resolution errors that happen during deferred name resolution also do not invoke a CATCH block. These errors are returned to the application or batch that called the error-generating routine.

Other  
  •  SQL Server 2008 : Transact-SQL Programming - The APPLY Operator
  •  SQL Server 2008 : Transact-SQL Programming - PIVOT and UNPIVOT
  •  SQL Server 2008 : Transact-SQL Programming - Ranking Functions
  •  SQL Server 2008 : Transact-SQL Programming - Common Table Expressions
  •  SQL Server 2008 : Transact-SQL Programming - The OUTPUT Clause
  •  SQL Server 2008 : Transact-SQL Programming - TOP Enhancements
  •  SQL Server 2008 : Transact-SQL Programming - The max Specifier
  •  SQL Server 2008 : T-SQL Tips and Tricks (part 3) - Generating T-SQL Statements with T-SQL & De-Duping Data with Ranking Functions
  •  SQL Server 2008 : T-SQL Tips and Tricks (part 2) - Using CONTEXT_INFO & Working with Outer Joins
  •  SQL Server 2008 : T-SQL Tips and Tricks (part 1) - Date Calculations & Sorting Results with the GROUPING Function
  •  
    programming4us
     
     
    programming4us