Delete a record if it exists in SQL SERVER 2018



  • Good morning, I am having problems when I try to delete 1 record, when it exists, but when executing it only shows me the message but it does not delete it.

    This is my query that I execute within a stored procedure where the parameters are sent from an application made in visual studies

    if(@tipo=11)begin
    
        --execute AUTONORT_UBICACION_PRODUCTOS 9,'001','001','A','01','F','004','04A'
                        
        BEGIN TRY--en caso ocurra un error inesperado
        BEGIN TRANSACTION
        DECLARE @FLAG_DELETE CHAR(1) = ''
            set @idUbicacion=RTRIM(@IDZONA)+RTRIM(@IDPAS)+'-'+RTRIM(@IDEST)+'-'+ltrim(rtrim(@IDFILCOL))
            
    
            if not exists(select u.IDUBICACION from PRODUCTO_UBICACION u where u.IDSUCURSAL=RTRIM(@IDSUC) and u.IDALMACEN=RTRIM(@IDALM) and u.IDPRODUCTO=RTRIM(@IDPRODUCTO))begin
    
                insert into PRODUCTO_UBICACION(idempresa,idsucursal,idalmacen,IDPRODUCTO,IDUBICACION,SINCRONIZA,FECHACREACION)            
                values(@IDEMP,RTRIM(@IDSUC),RTRIM(@IDALM),RTRIM(@IDPRODUCTO),@idUbicacion,'L',GETDATE())
            end 
            else begin
                    
                    SET @error = 'YA TIENE UNA ASIGNACION REGISTRADA''YA CUENTA CON UNA ASIGNACION REGISTRADA,PERO NO CUMPLE CON EL  ESTANDAR ... SE PROCEDERA A ELIMINA ESTE REGISTRO. 
                    INTENTE NUEVAMENTE LA ASGINACION DEL PRODUCTO'
                        DELETE  
                    FROM PRODUCTO_UBICACION 
                    WHERE IDSUCURSAL=RTRIM(@IDSUC) and IDALMACEN=RTRIM(@IDALM) and IDPRODUCTO=RTRIM(@IDPRODUCTO)
                raiserror(@error,16,1)
                return(99)  
                
                end
            
        COMMIT TRANSACTION
            
        END TRY
        BEGIN CATCH---mostrar mensaje de error de la transicion
                set @error=ERROR_MESSAGE()
                raiserror( @error,16,1)
    
    
        ROLLBACK TRANSACTION
        END CATCH
    end
    

    Maybe I'm misapplying the syntax, any other ideas on how to perform this process.



  • The RAISERROR after the DELETE will enter the CATCH block and rollback the transaction. The RETURN statement after the RAISERROR will not be executed.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2