Wednesday, September 22, 2010

Using TRY-CATCH in SQL Server 2008.

A TRY-CATCH construct include a TRY block Followed by a CATCH block. A TRY block is a group of SQL Statements enclosed in a batch, stored procedure, trigger, or function. If an error occurs in any statement of the TRY block, the control is passed to another group of statements that is enclosed in a CATCH block.
A CATCH block contains SQL statements that perform some operations when an error occurs. Therefore, an associated CATCH block must immediately follow a TRY block.

The syntax for TRY-CATCH is: -

BEGIN TRY
  {sql statement block}
END TRY
BEGIN CATCH
  [{sql statement block}]
END CATCH;

NOTE: - IF there are no errors in the code that is enclosed in TRY block, the control is passed to the statements immediately after the associated END CATCH statement. In this case, statements enclosed in the CATCH block are not executed.
       The TRY…..CATCH constructs can be nested. Either a TRY block or a CATCH block can contain nested TRY…CATCH constructs.  A CATCH block can contain an embedded TRY….CATCH construct to handle errors encountered by the CATCH code.

In the CATCH block, you can use the following system function to determine information about the errors.
  •   ERROR_LINE (): - Returns the line number at which the error occurred.
  •    ERROR_MESSAGE (): - Specifies the text of the message that would be returned to the application. The text includes the values supplied for any substitutable parameters. Such as lengths, object names, or times.
  •  ERROR_NUMBER (): - Returns the error number.
  •   ERROR_PROCEDURE (): - Returns the name of the stored procedure or trigger in which the error occurred. This function returns NULL if the error did not occur within a stored procedure or trigger.
  •   ERROR_ SEVERITY (): Returns the severity.
  •  ERROR_STATE (): - Returns the state of the error.

Example of TRY-CATCH.

BEGIN TRY
  declare @sql nvarchar(max)
  declare @sqlvariable int
  set @sqlvariable = 100
  select @sql = 'The SQL variable value is ' + @sqlvariable + '.'
  select @sql
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;

Following Result shown.

No comments:

Recent Posts