Hi Experts,

I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.

Link: 

http://stackoverflow.com/questions/485581/generate-delete-statement-from-foreign-key-relationships-in-sql-2008

--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child
--When I am deleting a grand child table, it should be linked to child table first followed by Parent

--- query 1
DELETE Top(100000) FROM [dbo].[ChartInstanceCase]
FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID]
INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId]
WHERE [Patient].PracticeID = '55';



--Query 2
DELETE Top(100000) [dbo].[ChartInstanceCase]
FROM  [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID] 
INNER JOIN [dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID]
WHERE [Patient].PracticeID = '55';

Please do let me know how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2

Please help.

Thanks,

Naveen

You can use CASCADE ON DELETE instead of that script. Please take a look at this URL:

Cascading Referential Integrity Constraints

can you use cascade on delete/update 

try this 

create table Main1 (sno1 int primary key,sname1 varchar(20))
Create table Main2 (sno2 int primary key,sname2 varchar(20),sno1 int)
create table Main3 (sno3 int primary key,sname3 varchar(20),sno2 int)

Alter table Main2 Add constraint FK_Main2 Foreign Key(sno1) References Main1(sno1) ON update cascade on delete cascade

Alter table Main3 Add constraint FK_Main3 Foreign Key(sno2) References Main2(sno2) ON update cascade on delete cascade

Insert into Main1 (sno1,sname1) values (1,'dallas')
Insert into Main2 (sno2,sname2,sno1) values (11,'Texas',1)
Insert into Main3 (sno3,sname3,sno2) values (111,'USA',11)
--values before delete
select * from Main1
Select * from Main2
Select * from Main3

Delete from Main1 where sno1=1
--values after delete
select * from Main1
Select * from Main2
Select * from Main3

--cleanup
Drop table Main3
drop table main2
drop table main1

This is all right.

But I am asked to cleanup an existing database where I do not have this constraint.

But I am asked to cleanup an existing database where I do not have this c

FK constraints are there between the tables, but ON DELETE CASCADE is not present. Whatever the SP, I have mentioned earlier identifies the Parent and Child relationships and derives a delete statement, but order of the INNER JOIN is not proper. I would like to set proper order. You can see my Query 1 and Query 2 for details.

Please help me.

Hi Naveen,

As I went through the Stored procedure in your link, I found the JOIN clause is recursively concatenated from Parent table(in this case, Patient) till the last grand table(ChartInstanceCase), so it is not easy to reverse the JOIN order.

In addition, the inner JOIN order doesn't make much sense here. The query optimizer will reorganize the order according to statistics for indexes and other stuff.

If you have any question, feel free to let me

are you looking at something like this?

http://visakhm.blogspot.ae/2011/11/recursive-delete-from-parent-child.html

Hi Eric,

Thanks for the reply. 

As per my view, Query 1 and Query 2 would do the same, that is rows from the grand child (ChartInstanceCase) would be deleted based on the parent table (Patient) based on filter condition PracticeID = '55'.

Please do analyze the above 2 queries and let me know your opinion. 

Thanks in advance for your valuable time.

I am getting the following error when I run the query,

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.