SP Transaction and insert

  var userAgent = HttpContext.Current.Request.UserAgent.ToLower();
Request.ServerVariables["REMOTE_ADDR"].ToString()
 IF EXISTS(SELECT 1 FROM tblCity WHERE CityName = @CityName )--and fkstateId =@fkstateId )
BEGIN
select 'record already exists.'
END
 else
              begin
               insert into tblCity(CityName,fkstateId)
                                     values(@CityName,@fkstateId)
                  select 'Record added successfully.'
              end  

------------

Begin transaction    
  --  Your Insert / Update Statement Here
  If (@@Error <> 0)   -- Check error
     Begin        
        rollback transaction      
     End
   else
       commit transaction