IF OBJECT_ID ( N’usp_ExampleProc’, N’P’ ) IS NOT NULL
DROP PROCEDURE usp_ExampleProc;
GO

— Create a stored procedure that will cause an
— object resolution error.
CREATE PROCEDURE usp_ExampleProc
BEGIN TRY
BEGIN TRAN
—Comandos SQL que precisam ser completados
COMMIT TRAN
END TRY

BEGIN CATCH
ROLLBACK TRAN

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, — Message text.
@ErrorSeverity, — Severity.
@ErrorState — State.
);
END CATCH;

Referência:
http://msdn.microsoft.com/pt-br/library/ms175976.aspx
http://msdn.microsoft.com/pt-br/library/ms177497.aspx