Error handling in SQL Server

SQL Server provides the following functions/commands to help in troubleshooting Transact-SQL code:
@@ERROR function
The @@ERROR function is  used to capture the number of an error generated by the previous Transact-SQL statement. A value of 0 indicates success. Though this function is quite handy, it is a little tricky because its value gets updated for every command. So you will have to save the value of this function to a variable to make use of it in the code.
@@ROWCOUNT function
The @@ROWCOUNT function returns the number of rows affected by the last Transact-SQL statement. Like the@@ERROR function, the value of @@ROWCOUNT gets updated for every command but can be captured in variable for use in the code.
RAISERROR function
This function can be used to return custom error messages when troubleshooting errors in Transact-SQL code. Here is the syntax:
RAISERROR (msg_ id or msg_str, severity, state, optional arguments) WITH option

• msg_id - A user-defined error message number stored in the sys.messages .
• message - A user-defined error message.
• severity - The severity level associated with the message. The severity code used you’ll use most of the time will be 16 indicating critical error. Please note that 11 is the minimum severity that will cause a CATCH block to fire.
• state - error state
• Argument - Parameters used in the substitution for variables defined in msg_str  or the message corresponding to msg_id
• WITH option - There are three values that can be used with this option:

• LOG - Logs error in the error log and the application log
• NOWAIT - Sends the message immediately to the client
• SETERROR - Sets the @@ERROR value to msg_id or 50000

Example:

USE [TestDB]
CREATE PROCEDURE [dbo].[UpdateEmployees]
(
@DepartmentID INT,
@EmpID INT
)
AS
BEGIN
DECLARE @ErrorMsg INT
DECLARE @RecordCount INT
UPDATE dimEmployees SET DepartmentID = @DepartmentID WHERE EmpID = @EmpID
SELECT  @ErrorMsg = @@ERROR, @RecordCount = @@ROWCOUNT
IF (@ErrorMsg <> 0 OR @RecordCount = 0)
BEGIN
RAISERROR ('An error occured when updating the dimEmployees table',16,1)
END
END

Try...Catch:
Try Catch technique consists of a Try block which contains the code that might cause an exception and a Catch block where the control will be transferred if an error is encountered in the Try block. If there are statements in the TRY block that follow the  statement that caused the error, they will not be executed.

BEGIN TRY
{ SQL statements block }
END TRY
BEGIN CATCH
{ Error Handling Code }
END CATCH

Example:

BEGIN TRY
SELECT 1/0;
PRINT 'Statement after error'
END TRY
BEGIN CATCH
PRINT 'Divide by zero error'
END CATCH

And here is the output:
Divide by zero error