Procedure delete Join between tables

Asked

Viewed 322 times

0

Good afternoon, I have the following Tb environment.Client where a client has N processes in Tb.Process, N movements in Tb.Progress and N attachments in Tb.Annex.

inserir a descrição da imagem aqui

I need to mount an sql statement that when deleting a client by code "ClienteID", delete all linked processes, schedules and attachments. Being that the Tb.Andamento and Tb.Anexo are linked to Tb.Processo pelo "NumeroProcesso"

CREATE PROCEDURE delete_Cliente

    @ClienteID int

AS
BEGIN
        SELECT * FROM Andamento WHERE NumeroProcesso IN         
        (SELECT NumeroProcesso FROM Processo WHERE ClienteID = @ClienteID)
        SELECT * FROM Anexo WHERE NumeroProcesso IN         
        (SELECT NumeroProcesso FROM Processo WHERE ClienteID = @ClienteID)
        SELECT * FROM ArquivoAnexoAcordo WHERE NumeroProcesso IN        
        (SELECT NumeroProcesso FROM Processo WHERE ClienteID = @ClienteID)
        SELECT * FROM HistoricoNegociacao WHERE NumeroProcesso IN       
        (SELECT NumeroProcesso FROM Processo WHERE ClienteID = @ClienteID)

        SELECT * FROM Cliente WHERE ClienteID = @ClienteID
        SELECT * FROM Processo WHERE ClienteID = @ClienteID


        DELETE Andamento WHERE NumeroProcesso IN                
        (SELECT NumeroProcesso FROM Processo WHERE ClienteID = @ClienteID)
        DELETE Anexo WHERE NumeroProcesso IN                
        (SELECT NumeroProcesso FROM Processo WHERE ClienteID = @ClienteID)
        DELETE ArquivoAnexoAcordo WHERE NumeroProcesso IN               
        (SELECT NumeroProcesso FROM Processo WHERE ClienteID = @ClienteID)
        DELETE HistoricoNegociacao WHERE NumeroProcesso IN              
        (SELECT NumeroProcesso FROM Processo WHERE ClienteID = @ClienteID)

        DELETE Processo WHERE ClienteID = @ClienteID
        DELETE Cliente WHERE ClienteID = @ClienteID

END
GO
sql-server tsql

2 answers

0


Evandro the only problem I saw really serious in your routine is that you don’t have a transaction, IE, if an error occurs in one of the steps, the above will not be undone.

I included a transaction and improved the queries with a parameter and not a subquery. Furthermore, I think it will work

    CREATE PROCEDURE delete_Cliente

    @ClienteID int

   AS
   BEGIN

    Declare @NumProcesso varchar(50)

    SELECT @NumProcesso = NumeroProcesso FROM Processo WHERE ClienteID = @ClienteID

    BEGIN TRANSACTION

            DELETE ArquivoAnexoAcordo WHERE NumeroProcesso = @NumProcesso               
            DELETE HistoricoNegociacao WHERE NumeroProcesso = @NumProcesso
            DELETE Andamento WHERE NumeroProcesso = @NumProcesso
            DELETE Anexo WHERE NumeroProcesso = @NumProcesso

            DELETE Processo WHERE ClienteID = @ClienteID
            DELETE Cliente WHERE ClienteID = @ClienteID

    Commit transaction

    IF @@Error > 0
    BEGIN
        ROLLBACK TRANSACTION
        PRINT 'ERRO AO EXCLUIR CLIENTE'

    END

   END

  • perfect! thanks for the instructions

  • 1

    Note that a client may have more than one process. That must be why in the original @Evandro code there was a sub-query for each DELETE.

  • Indeed, if there is more than one process, a subselect or Join should be made

0

If a client can have more than one process, then it is necessary to be attentive to that detail. The following suggestion first notes all client processes for only after deleting table rows.

-- código #1
CREATE PROCEDURE delete_Cliente
     @ClienteID int as
begin

-- anota todos os processos do cliente
declare @Processos_Cliente table (NumeroProcesso int primary key);

INSERT into @Processos_Cliente
  SELECT NumeroProcesso
    from Processo
    where ClienteID = @ClienteID;

BEGIN TRANSACTION;

-- apaga andamento
DELETE Andamento 
  where NumeroProcesso in (SELECT NumeroProcesso from @Processos_Cliente);

-- apaga anexo
DELETE Anexo
  where NumeroProcesso in (SELECT NumeroProcesso from @Processos_Cliente);

-- apaga linha(s) do processo
DELETE Processo
  where NumeroProcesso in (SELECT NumeroProcesso from @Processos_Cliente);

-- apaga linha do cliente
DELETE Cliente
  where ClienteID = @ClienteID;

COMMIT;

end;
go

Code #1 includes the tables Client, Process, Progress and Attachment, which are described in the diagram.

Browser other questions tagged sql-server tsql

You are not signed in. Login or sign up in order to post.