Multiple Delete query in procedure in sql server

Question

I have around 40 delete query statement in a procedure for delete record from multiple tables for foreign key records.

example

   Create Proc usp_delete_record(@id int)
   as
   Begin

   Delete from table1 where id=@id;
   Delete from table2 where id=@id;
   Delete from table3 where id=@id;
   Delete from table4 where id=@id;
   Delete from table5 where id=@id;
   Delete from table6 where id=@id;
   Delete from table7 where id=@id;
   Delete from table8 where id=@id;
   ....................

   .................
   Delete from table40 where id=@id;


   End

It is very slow or hang execution.

How to handle this?

Check the below link to delete from multiple tables using single query : stackoverflow.com/questions/20417303/… — Paresh J
– Paresh J, Commented Oct 8, 2014 at 5:49

Recursive · Accepted Answer · 2014-10-08 05:58:24Z

1

no needed of a procedure since u have it as a foreign key. just delete in the parent table with cascade.

so recreate your foriegn key for all child tables as

ALTER TABLE <childtables>  WITH CHECK 
ADD CONSTRAINT <fk_blah_blah> FOREIGN KEY(id)
REFERENCES <parenttable> (id)
ON DELETE CASCADE

Once you have this in place, you can delete the single record in parent table and so all 40 tables data are deleted

edited Oct 8, 2014 at 5:58

answered Oct 8, 2014 at 5:52

Recursive

9528 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

knkarthick24 · Accepted Answer · 2014-10-08 05:52:16Z

0

USE ON DELETE CASCADE:

    CREATE TABLE parent (parent_id integer primary key );

CREATE TABLE child (child_name TEXT primary key,parent_id integer REFERENCES parent (parent_id) ON DELETE CASCADE);

answered Oct 8, 2014 at 5:52

knkarthick24

3,24619 silver badges21 bronze badges

Collectives™ on Stack Overflow

Multiple Delete query in procedure in sql server

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related