CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Rakesh
Rakesh

Reputation: 1

Multiple Delete query in procedure in sql server

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?

Upvotes: 0

Views: 1210

Answers (2)

Recursive
Recursive

Reputation: 952

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

Upvotes: 1

knkarthick24
knkarthick24

Reputation: 3216

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);

Upvotes: 0

Related Questions