title: Is this a good way to add transaction to sql ?
body: <p>Is this a good way to add TRANSACTION to my code. I have to first update my code and if it fails then insert should also not work. Have a look please if it's a correct way or not. If not then please improve.</p>
  
  <pre><code>Begin Transaction[Transaction1]
  
      Begin Try
  
           IF(@ServiceInfoToJobStatus = 1)
              Update ServiceInfo
              Set ServiceInfoToJobStatus= 0 --To set all current jobs to prior because one person cannot have many jobs selected as current
              Where @ServiceInfoToJobStatus = 1 AND ServiceInfo.fk_PersonalInfo_ServiceInfo_PID= @fk_PersonalInfo_ServiceInfo_PID
  
              Set @ServiceInfoEntryDateTime= (Select GetDate())
  
                Insert into dbo.ServiceInfo
                (
  
                  ServiceInfoInitialDesignation,
                  ServiceInfoInitialBPS,
                  fk_Districts_ServiceInfo_InitialDistrictID,
                  ServiceInfoJobStatus,
                  ServiceInfoFromDate,
                  ServiceInfoDepartment,
                  fk_PersonalInfo_ServiceInfo_PID,
                  ServiceInfoServiceType  ,
                  ServiceInfoOffice   ,
                  ServiceInfoCadre    ,
                  fk_WebUsers_ServiceInfo_UserID,
                  ServiceInfoEntryDateTime,
                  ServiceInfoToDesignation        ,
                  ServiceInfoToBPS        ,
                  fk_Districts_ServiceInfo_ToDistrictID       ,
                  ServiceInfoToJobStatus      ,
                  ServiceInfoToDate       ,
                  ServiceInfoToDepartment     ,
                  ServiceInfoToServiceType        ,
                  ServiceInfoToOffice     ,
                  ServiceInfoToCadre      
                )
  
                Values 
                (
  
                  @ServiceInfoInitialDesignation,
                  @ServiceInfoInitialBPS,
                  @fk_Districts_ServiceInfo_InitialDistrictID,
                  @ServiceInfoJobStatus,
                  @ServiceInfoFromDate,
                  @ServiceInfoDepartment,
                  @fk_PersonalInfo_ServiceInfo_PID,
                  @ServiceInfoServiceType ,
                  @ServiceInfoOffice  ,
                  @ServiceInfoCadre   ,
                  @fk_WebUsers_ServiceInfo_UserID,
                  Convert(varchar, @ServiceInfoEntryDateTime, 113),
                  @ServiceInfoToDesignation       ,
                  @ServiceInfoToBPS       ,
                  @fk_Districts_ServiceInfo_ToDistrictID      ,
                  @ServiceInfoToJobStatus     ,
                  @ServiceInfoToDate      ,
                  @ServiceInfoToDepartment        ,
                  @ServiceInfoToServiceType       ,
                  @ServiceInfoToOffice        ,
                  @ServiceInfoToCadre     
                )
  
                Set @ReturnStatus = 1
  
  
      Commit Transaction[Transaction1]
  
      End Try
  
      Begin Catch
  
                ROLLBACK Transaction[Transaction1]
  
                Set @ReturnStatus= 0 
                Set @ReturnStatusMessage= (Select ERROR_MESSAGE())              
  
      End Catch
  </code></pre>