New error handling method in SQLServer procedures from version 2005. This is the better way of handling errors in SQLServer.

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
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;
GO




BEGIN TRY

BEGIN TRANSACTION -- Start the transaction

--insert
INSERT INTO....

--update
UPDATE INTO....



-- Generate divide-by-zero error.
SELECT 1/0;

-- If we reach here, success!
IF @@TRANCOUNT > 0
COMMIT TRANSACTION


END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;

END CATCH;