Following is code snipest which tells how to handle errors in SQL , T-SQL using Try Catch block. The code is self explanatory. This works excellent with Insert, Delete and Update SQL commands. Tested with Microsoft SQL server 2008.
USE [databasname]
BEGIN TRY
BEGIN TRANSACTION
Script code here;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
