Looping

Is there any way to do a loop in a stored procedure?
I have a "ripple effect" delete I need to and appearantly you can't do a
join in a delete statement.
A->B->C->D
Table B is relationally tied to A
Table C is relationally tied to B
Table D is relationally tied to C
If I delete a record in Table A, all records in B, C & D that are tied
should get deleted.There's no need to do a loop. Simply execute four set-based delete
statements in a transaction:
BEGIN TRAN
DELETE D WHERE CK IN (SELECT CK FROM C JOIN B ON (C.BK = B.BK) JOIN A ON
(B.AK = A.AK) WHERE ...)
DELETE C WHERE BK IN (SELECT BK FROM B JOIN A ON (B.AK = A.AK) WHERE ...)
DELETE B WHERE AK IN (SELECT AK FROM A WHERE ...)
DELETE A WHERE ...
COMMIT
"EdB" <EdB@.discussions.microsoft.com> wrote in message
news:E7D81D0A-F330-4DFA-B7EE-5BF916E231EE@.microsoft.com...
> Is there any way to do a loop in a stored procedure?
> I have a "ripple effect" delete I need to and appearantly you can't do a
> join in a delete statement.
> A->B->C->D
> Table B is relationally tied to A
> Table C is relationally tied to B
> Table D is relationally tied to C
> If I delete a record in Table A, all records in B, C & D that are tied
> should get deleted.
>|||Thanks. I knew that there had to be a way to do it.
"Brian Selzer" wrote:

> There's no need to do a loop. Simply execute four set-based delete
> statements in a transaction:
> BEGIN TRAN
> DELETE D WHERE CK IN (SELECT CK FROM C JOIN B ON (C.BK = B.BK) JOIN A ON
> (B.AK = A.AK) WHERE ...)
> DELETE C WHERE BK IN (SELECT BK FROM B JOIN A ON (B.AK = A.AK) WHERE ...)
> DELETE B WHERE AK IN (SELECT AK FROM A WHERE ...)
> DELETE A WHERE ...
> COMMIT
>
> "EdB" <EdB@.discussions.microsoft.com> wrote in message
> news:E7D81D0A-F330-4DFA-B7EE-5BF916E231EE@.microsoft.com...
>
>|||>> have a "ripple effect" delete I need to and appearantly you can't do a j
oin in a delete statement <<
Look up Declarative Referential Integrity actions. You can use primary
and foreign keys to set up a chain that will maintain the relationship.|||Brian,
Very elegant solution, thanks for providing it.
Best regards,
Sean Gahan
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uoh6M3GJGHA.424@.TK2MSFTNGP12.phx.gbl...
> There's no need to do a loop. Simply execute four set-based delete
> statements in a transaction:
> BEGIN TRAN
> DELETE D WHERE CK IN (SELECT CK FROM C JOIN B ON (C.BK = B.BK) JOIN A ON
> (B.AK = A.AK) WHERE ...)
> DELETE C WHERE BK IN (SELECT BK FROM B JOIN A ON (B.AK = A.AK) WHERE ...)
> DELETE B WHERE AK IN (SELECT AK FROM A WHERE ...)
> DELETE A WHERE ...
> COMMIT
>
> "EdB" <EdB@.discussions.microsoft.com> wrote in message
> news:E7D81D0A-F330-4DFA-B7EE-5BF916E231EE@.microsoft.com...
>