"Do until" loop in a Stored procedure?

I'm pretty new to all this about Stored Procedures.. 开发者_运维技巧What i want to accomplish is a query and then loop thru all the returned rows.. I thought about somting like this:

ALTER PROCEDURE usp_findDoor
@door int,
AS
DECLARE @foundId int
SELECT @foundId=Id FROM pd_orders WHERE door=@door

For each id in above Query i want to delete in another table like this

DELETE FROM pd_md_detials WHERE order_id = @foundId

Now i do all this by ASP code and it looks like this

Set db = con.execute("Select id from pd_orders WHERE door=1")
Do UNTIL db.EOF
  Con.execute("DELETE FROM pd_md_details WHERE order_id="& db("id"))
  db.movenext
LOOP

Is this even possible in a SP or do i have to rethink?

Edited Section

If i have tables on three levels like

pd_order_ecolid -> pd_mounting_details -> pd_inventory

And the related columns for above tables are

ID -> order_data -> pp_id

Then it should be possible with this line to delete all rows in table pd_inventory

DELETE FROM pd_inventory
WHERE pp_id IN 
  (
     SELECT id FROM pd_mounting_details 
     WHERE order_data IN 
       (
         SELECT id from pd_order_ecolid 
         WHERE order_head=@order_head AND order_line=@order_line
       )
  )

im not able to try it myself seens im not athome (but i cant stop thinking about how to fix this)

Best regards

---

Am i missing something? You want to DELETE every record WHERE door=@door ?

Why not change your SQL:

DELETE FROM pd_colors WHERE door=@door - This deletes the tuple

---

If you simply want to delete where door=@door you can just;

delete from pd_colors where door=@door

If you want to loop to combine a fetch of the deleted rows you can instead;

delete from pd_colors output deleted.* where door=@door