Best Practice for using a transaction

http://msdn.microsoft.com/en-us/library/ms175976.aspx







USE 


AdventureWorks


;






GO






BEGIN


 TRANSACTION


;













BEGIN


 TRY



    


--


 


Generate


 a constraint violation error


.






    DELETE FROM 


Production


.


Product






    WHERE 


ProductID


 


=


 


980


;









END


 TRY






BEGIN


 CATCH



    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


;










    IF 


@


@TRANCOUNT


 


>


 


0






        ROLLBACK TRANSACTION


;









END


 CATCH


;










IF 


@


@TRANCOUNT


 


>


 


0






    COMMIT TRANSACTION


;






GO