CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Mateo Rodríguez
Mateo Rodríguez

Reputation: 15

How to delete multiple rows in diferent tables?

I have a little problem, how could I improve my QUERY, I want to delete multiple rows from multiple tables using the foreign key with this :

CREATE PROCEDURE SCOUTL.DeleteRegistroUsuario(@ID INT)
AS
     BEGIN
         DECLARE @Evidencia AS UNIQUEIDENTIFIER;
         SET @Evidencia =
(
    SELECT e.IdEvidenciaPorPractica
    FROM SCOUTL.EvidenciaPorPractica e
         INNER JOIN SCOUTL.RegistroUsuario r ON e.IdEvidenciaPorPractica = r.IdEvidenciaPorPracticaFK
    WHERE r.IdRegistroUsuario = @ID
);
         DELETE FROM SCOUTL.RegistroUsuario
         WHERE IdRegistroUsuario = @ID;
         DELETE FROM SCOUTL.EvidenciaPorPractica
         WHERE IdEvidenciaPorPractica = @Evidencia;
     END;

But, when i execute this query, return this:

Incorrect syntax near @ID

Please, I need corrections

Upvotes: 0

Views: 62

Answers (3)

Kaval Patel
Kaval Patel

Reputation: 668

Try This:

 CREATE PROCEDURE SCOUTL.DeleteRegistroUsuario(@ID INT)
AS
BEGIN

    DELETE e FROM SCOUTL.EvidenciaPorPractica AS e
    INNER JOIN SCOUTL.RegistroUsuario r ON e.IdEvidenciaPorPractica = r.IdEvidenciaPorPracticaFK
    WHERE r.IdRegistroUsuario = @ID

    DELETE FROM SCOUTL.RegistroUsuario
    WHERE IdRegistroUsuario = @ID;

END;

Upvotes: 0

DatabaseCoder
DatabaseCoder

Reputation: 2032

We can use joins with delete statement to do this -

CREATE PROCEDURE SCOUTL.DeleteRegistroUsuario(@ID INT)
AS
BEGIN
    DELETE e
    FROM SCOUTL.EvidenciaPorPractica e
    INNER JOIN SCOUTL.RegistroUsuario r ON
         e.IdEvidenciaPorPractica = r.IdEvidenciaPorPracticaFK
    WHERE r.IdRegistroUsuario = @ID

    DELETE FROM SCOUTL.RegistroUsuario
    WHERE IdRegistroUsuario = @ID
END;

If you want to delete foreign key tables data automatically, you can look into On Delete Cacade

Upvotes: 1

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

Try this

CREATE PROCEDURE dbo.DeleteRegistroUsuario
(
    @ID INT
)
AS
BEGIN

    DELETE E
       FROM SCOUTL.EvidenciaPorPractica e
          INNER JOIN SCOUTL.RegistroUsuario r 
             ON e.IdEvidenciaPorPractica = r.IdEvidenciaPorPracticaFK
       WHERE r.IdRegistroUsuario = @ID

    DELETE FROM SCOUTL.RegistroUsuario WHERE IdRegistroUsuario = @ID;

END;

Upvotes: 0

Related Questions