How to execute two delete statments

Question

    ALTER PROCEDURE dbo.ModeratorSpamDeleteThread

  @ThreadsID INT

AS

  DELETE Threads
  WHERE ThreadsID=@ThreadsID

I have got this..But it tells me that i cant delete the table cause it is connected to another table by foreign key..

So I need to do this too:

  @ThreadsID INT

AS

  DELETE Comments
  WHERE ThreadsID=@ThreadsID

The question is how do i Combine both stamtents into one?

Look into ON DELETE CASCADE for foreign keys. – Bala R Jul 15 '11 at 21:33 — Bala R, Jul 15 '11 at 21:33

score 2 · Accepted Answer · answered Jul 15 '11 at 21:33

2

You first execute the delete in the Comments and after that you execute the delete in Threads.

ALTER PROCEDURE dbo.ModeratorSpamDeleteThread
  @ThreadsID INT
AS

DELETE Comments
WHERE ThreadsID=@ThreadsID 

DELETE Threads
WHERE ThreadsID=@ThreadsID

answered Jul 15 '11 at 21:33

Mikael Eriksson

136,425
22
210
281

I would be sure to add rollback functionality to that, in case the second statement would fail. – Avada Kedavra Jul 15 '11 at 21:36
@Avada Kedavra - That is a good idea. I usually have the transactions started in the middle tier/client instead. – Mikael Eriksson Jul 15 '11 at 21:39
rollback.. I am not an sql practitioner..But i can see that method working the whole time...That is unless..both moderators delete the thread simultaneously...Am I right? – Matrix001 Jul 15 '11 at 21:53
@Matrix001 - The rollback could come in handy if the second delete fail for some reason. If you execute both delete statements in the same transaction you can do a rollback if the delete fails and you would get back the deleted rows from Comments. A reason for the delete in Threads to fail could be that you have other tables with FK relation to Threads that you have forgot to delete. Have a look at this http://www.sommarskog.se/error_handling_2005.html and this answer http://stackoverflow.com/questions/2073737/nested-stored-procedures-containing-try-catch-rollback-pattern/2074139#2074139 – Mikael Eriksson Jul 15 '11 at 22:00

score 1 · Answer 2 · answered Jul 15 '11 at 21:36

1

This is how you can do it:

ALTER PROCEDURE dbo.ModeratorSpamDeleteThread
@ThreadsID INT
AS

  DELETE Comments
  WHERE ThreadsID=@ThreadsID

  DELETE Threads
  WHERE ThreadsID=@ThreadsID

GO

answered Jul 15 '11 at 21:36

kheya

7,546
20
77
109

How to execute two delete statments

2 Answers2