Use Try...Catch Block and Transactions in SQL Server

Use Try...Catch Block and Transactions in Stored Procedure as Followes :

Code :

/******************************************************************************    
-----------------------  
Developers Note   
-----------------------  
Object          : StoredProcedure [dbo].[sp_SetUsersDetails]     
Script Date     : 19/11/2012   
Developed By    : Rohan Sharma
Used For        : Add/Update User
Implementaion   :   

    sp_SetUsersDetails 5, 'Rohan Sharma', 'Block-B', '87867675765'

*****************************************************************************/ 
 
CREATE PROCEDURE [dbo].[sp_SetUsersDetails] 
    -- Parameters Here
    @ID   INT, 
    @Name  NVARCHAR(500), 
    @Address NVARCHAR(500) = NULL, 
    @Phone  NVARCHAR(500) = NULL, 
AS  
BEGIN  

    SET NOCOUNT ON;  

    BEGIN TRANSACTION UpdateLMS

    BEGIN TRY

        -- Put Your Tansaction DB Codes Here

    END TRY
    BEGIN CATCH

        -- Rollback Transaction If any Transaction Occured
        IF @@ROWCOUNT > 0
            ROLLBACK TRANSACTION UpdateLMS

        -- Put Any ErrorLog Codes Here

    END CATCH

    -- Commit Transaction If any Transaction Occured

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION UpdateLMS

    SET NOCOUNT OFF;  

END