SQLServer Exception Handling

General Handling form

  BEGIN TRY
    BEGIN TRANSACTION;
 XXX
 XXX
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION;

    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
  END CATCH

Example

USE AdventureWorks2012;
GO

IF OBJECT_ID('UpdateSales', 'P') IS NOT NULL
DROP PROCEDURE UpdateSales;
GO

CREATE PROCEDURE UpdateSales
  @SalesPersonID INT,
  @SalesAmt MONEY = 0
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION;
      UPDATE LastYearSales
      SET SalesLastYear = SalesLastYear + @SalesAmt
      WHERE SalesPersonID = @SalesPersonID;
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;

    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
    PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
  END CATCH
END;
GO

RAISERROR
When using RAISERROR, you should include an error message, error severity level, and error state.

The RAISERROR statement returns error information to the calling application.
As of SQL Server 2012 is was replaced by THROW.

RAISERROR syntax:
RAISERROR ( { msg_id | msg_str | @local_variable } { ,severity ,state } [ ,argument [ ,...n ] ] ) [ WITH option [ ,...n ] ]

msg_id
Is a user-defined error message number stored in the sys.messages catalog view using sp_addmessage. Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.

msg_str
Is a user-defined message with formatting similar to the printf function in the C standard library. The error message can have a maximum of 2,047 characters. If the message contains 2,048 or more characters, only the first 2,044 are displayed and an ellipsis is added to indicate that the message has been truncated.
When msg_str is specified, RAISERROR raises an error message with an error number of 50000.

@local_variable
Is a variable of any valid character data type that contains a string formatted in the same manner as msg_str. @local_variable must be char or varchar,

severity
Severity levels from 0 through 18 can be specified by any user.
Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions.
For severity levels from 19 through 25, the WITH LOG option is required.
Severity levels less than 0 are interpreted as 0.
Severity levels greater than 25 are interpreted as 25.
When severity is -1, the actual severity, is the severity value associated with the error.

state
Is an integer from 0 through 255.
You can specify -1 to return the value associated with the error.
If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors.

Arguments
Arguments are the parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters, but the total number of substitution parameters cannot exceed 20.
Each substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary.

Option
Can be one of the values in the following table.
LOG - Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.
NOWAIT -  Sends messages immediately to the client.
SETERROR - Sets the @@ERROR and ERROR_NUMBER values to msg_id or 50000, regardless of the severity level.

Examples
RAISERROR (N'This is message %s %d.', -- Message text.
           10, -- Severity,
           1, -- State,
           N'number', -- First argument.
           5); -- Second argument.
GO
-- The message text returned is: 
This is message number 5.

SQL Server 2000

The concept of TRY-CATCH blocks was introduced in SQL Server 2005.

In SQL Server 2000, for Error Handling, need to consistently check the value of the @@ERROR system variable. 

@@ERROR is a variable updated by the SQL Server after each statement.

This variable contains the corresponding error number.

For normal execution, the value of @@ERROR is 0.

These error numbers are stored in the sysmessages table in the master database.

@@ERROR value can be saved in an integer variable immediately after the Transact-SQL statement completes.
For most error handling purposes, need to handle cases when the value of @@ERROR is non-zero, which will indicate that an error occurred.


Reference
Differences Between RAISERROR and THROW in Sql Server
Handling Errors in SQL Server 2012
RAISERROR (Transact-SQL)